jdbc连接数据库实例详解

时间:2022-09-22 17:21:45

jdbc简介

jdbc全称为:java data base connectivity (java数据库连接),可以为多种数据库提供填统一的访问。jdbc是sun开发的一套数据库访问编程接口,是一种sql级的api。它是由java语言编写完成,所以具有很好的跨平台特性,使用jdbc编写的数据库应用程序可以在任何支持java的平台上运行,而不必在不同的平台上编写不同的应用程序。

jdbc编程步骤

(1)加载驱动程序:

下载驱动包 : http://dev.mysql.com/downloads/connector/j/

解压,得到 jar文件。将该文件复制到java工程目录java resources/libraries/ 下,→ buildpath 。

(2)获得数据库连接

(3)创建statement对象:

(4)向数据库发送sql命令

(5)处理数据库的返回结果(resultset类)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
package com.baidu.emp.jdbctest;
 
import java.sql.connection;
import java.sql.drivermanager;
import java.sql.resultset;
import java.sql.statement;
 
import com.mysql.jdbc.driver;
/**
 * 开始使用jdbc连接数据库
 * @author admin
 *
 */
public class test001 {
 
  public static void main(string[] args) throws exception {
 
    /**
     * 加载驱动
     */
    // 方法一:
    /*
     * import java.sql.drivermanager; import com.mysql.jdbc.driver;
     */
    // driver driver = new driver();
    // drivermanager.registerdriver(driver);
 
    // 方法二:(推荐使用)
    class.forname("com.mysql.jdbc.driver");
 
    /**
     * 创建链接
     */
    string url = "jdbc:mysql://localhost:3306/testjdbc";
    string user = "root";
    string password = "root";
    connection connection = drivermanager.getconnection(url, user, password);
 
    // 创建statement对象
    statement statement = connection.createstatement();
 
    /**
     * 执行sql,获取结果集
     */
    string sql = "select * from test01";
    resultset result = statement.executequery(sql);
 
    // 遍历结果集
    while (result.next()) {
      string name = result.getstring("name");
      int id = result.getint("id");
      system.out.println(name + "\t" + id);
    }
 
    /**
     * 关闭链接,释放资源
     */
    result.close();
    statement.close();
    connection.close();
  }
}

防止sql注入改用preparestatement

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
package com.boya.emp.jdbctest;
 
import java.sql.connection;
import java.sql.drivermanager;
import java.sql.preparedstatement;
import java.sql.resultset;
/**
 * sql注入,使用preparestatement对象进行预编译
 * @author admin
 *
 */
public class test002 {
 
  public static void main(string[] args) throws exception {
 
    /**
     * 加载驱动
     */
    class.forname("com.mysql.jdbc.driver");
 
    /**
     * 创建链接
     */
    string url = "jdbc:mysql://localhost:3306/testjdbc";
    string user = "root";
    string password = "root";
    connection connection = drivermanager.getconnection(url, user, password);
 
    // 写sql
    string sql = "select * from test01 where id = ?";
    //创建statement对象,预编译
    preparedstatement statement = connection.preparestatement(sql);
    //设置参数
    statement.setint(1, 2);
    /**
     * 执行sql,获取结果集
     */
    resultset result = statement.executequery();
 
    // 遍历结果集
    while (result.next()) {
      string name = result.getstring("name");
      int id = result.getint("id");
      system.out.println(name + "\t" + id);
    }
 
    /**
     * 关闭链接,释放资源
     */
    result.close();
    statement.close();
    connection.close();
  }
}

进行代码优化,设置配置文件,工具类,实现增删该查

增加配置文件方便修改数据库,用户登录。。。

jdbc.properties(配置文件名)

?
1
2
3
4
drivername=com.mysql.jdbc.driver
url=jdbc:mysql://localhost:3306/testjdbc
username=root
password=root

注意写配置文件时中间不可以有空格,引号之类的

工具类:增强了代码的复用性

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
package com.baidu.emp.utils;
 
import java.io.inputstream;
import java.sql.connection;
import java.sql.drivermanager;
import java.sql.preparedstatement;
import java.sql.resultset;
import java.sql.sqlexception;
import java.util.properties;
 
import org.junit.test;
 
 
 
public class jdbcutils {
 
  static string driverclassname;
  static string url;
  static string user;
  static string password;
 
  static {
    // 创建配置文件对象
    properties properties = new properties();
    // 加载配置文件输入流
    inputstream inputstream = jdbcutils.class.getclassloader().getresourceasstream("jdbc.properties");
    // 重新加载配置文件
    try {
      properties.load(inputstream);
      // 获取配置文件的值
      driverclassname = properties.getproperty("drivername");
      url = properties.getproperty("url");
      user = properties.getproperty("username");
      password = properties.getproperty("password");
      class.forname(driverclassname);
 
    } catch (exception e) {
      // 抛出异常
      throw new runtimeexception(e);
    }
  }
 
  /**
   * 获取连接
   */
  @test
  public void testname() throws exception {
     
    system.out.println(driverclassname);
  }
  public static connection getconnection() {
    connection connection = null;
    try {
      connection = drivermanager.getconnection(url, user, password);
    } catch (sqlexception e) {
      // 抛出异常
      throw new runtimeexception(e);
    }
    return connection;
  }
 
  /**
   * 关闭链接,释放资源
   */
  public static void close(connection connection, preparedstatement statement, resultset resultset) {
 
    try {
      if (resultset != null) {
        resultset.close();
      }
      resultset = null; // 垃圾及时清除
      //注意,不要弄成死循环
      close(connection, statement);
    } catch (sqlexception e) {
      throw new runtimeexception(e);
    }
 
  }
 
  /**
   * 增删改释放资源
   */
  public static void close(connection connection, preparedstatement statement) {
 
    try {
      if (connection != null) {
        connection.close();
      }
         
      connection = null;
      if (statement != null) {
        statement.close();
      }
      statement = null;
 
    } catch (sqlexception e) {
      throw new runtimeexception(e);
    }
 
  }
 
}

测试增删改查:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
package com.baidu.emp.jdbctest;
 
import java.sql.connection;
import java.sql.preparedstatement;
import java.sql.resultset;
 
import org.junit.after;
import org.junit.before;
import org.junit.test;
 
import com.baidu.emp.utils.jdbcutils;
 
/**
 * 使用jdbcutils连接数据库进行增删改查
 *
 * @author admin
 *
 */
public class test003 {
 
  // 初始化值
  connection connection = null;
  preparedstatement statement = null;
  resultset result = null;
 
  @before
  public void start() throws exception {
    // 创建链接
    connection = jdbcutils.getconnection();
    system.out.println("创建链接");
  }
 
  @after
  public void end() throws exception {
    // 关闭链接
    jdbcutils.close(connection, statement, result);
    system.out.println("关闭链接");
  }
   
  /**
   *插入数据
   * @throws exception
   */
  @test
  public void add() throws exception {
    string sql = "insert into test01 values(null,?)";
    statement = connection.preparestatement(sql);
    statement.setstring(1, "李四");
    int result = statement.executeupdate();
    if (result!=0) {
      system.out.println("添加成功");
    }
  }
  /**
   * 删除数据
   * @throws exception
   */
  @test
  public void del() throws exception {
    string sql = "delete from test01 where id =?";
    statement = connection.preparestatement(sql);
    statement.setint(1,3);
    int result = statement.executeupdate();
    if (result!=0) {
      system.out.println("删除成功");
    }
  }
  /**
   * 修改数据
   * @throws exception
   */
  @test
  public void change() throws exception {
    string sql = "update test01 set name = ? where id = ?";
    statement = connection.preparestatement(sql);
    statement.setstring(1, "张飞");
    statement.setint(2, 2);
    int result = statement.executeupdate();
    if (result!=0) {
      system.out.println("修改成功");
    }
  }
   
  /**
   * 查询全部数据
   * @throws exception
   */
  @test
  public void findall() throws exception {
    string sql = "select id , name from test01";
    statement = connection.preparestatement(sql);
    result = statement.executequery();
    if (result.next()) {
      system.out.println("查询成功");
    }
  }
   
  /**
   * 条件查询数据
   * @throws exception
   */
  @test
  public void findone() throws exception {
    string sql = "select id , name from test01 where id = ?";
    statement = connection.preparestatement(sql);
    statement.setint(1, 2);
    result = statement.executequery();
    if (result.next()) {
      system.out.println("查询成功");
    }
  }
 
}

以上就是相关知识以及相关代码,感谢大家对服务器之家的支持。