一、什么是JDBC
JDBC(Java Data Base Connectivity,java数据库连接)是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准,据此可以构建更高级的工具和接口,使数据库开发人员能够编写数据库应用程序。
二、JDBC编码步骤
0、前提:拷贝数据库的驱动到构建路径中(classpath)
1、注册驱动
2、获取与数据库的链接
3、创建代表SQL语句的对象
4、执行SQL语句
5、如果是查询语句,需要遍历结果集
6、释放占用的资源
三、代码实现
1、在终端或者navicat中创建一个表:users, 如下图:
2、创建一个名为JdbcDemo1的类,并导入包:mysql-connector-java-5.0.8-bin.jar, JdbcDemo1代码如下:
1 packagecom.bigshow1949.jdbc;2
3 importjava.sql.Connection;4 importjava.sql.DriverManager;5 importjava.sql.ResultSet;6 importjava.sql.SQLException;7 importjava.sql.Statement;8
9 /*
10 create table users(11 id int primary key auto_increment,12 name varchar(40),13 password varchar(40),14 email varchar(60),15 birthday date16 )character set utf8 collate utf8_general_ci;17
18 insert into users(name,password,email,birthday) values('zs','123456','zs@sina.com','1980-12-04');19 insert into users(name,password,email,birthday) values('lisi','123456','lisi@sina.com','1981-12-04');20 insert into users(name,password,email,birthday) values('wangwu','123456','wangwu@sina.com','1979-12-04');21 */
22
23 //JDBC的编码步骤:24 //查询users表中的所有数据,打印到控制台上
25 public classJdbcDemo1 {26
27 public static void main(String[] args) throwsSQLException {28 //1、注册驱动
29 DriverManager.registerDriver(newcom.mysql.jdbc.Driver());30 //2、获取与数据库的链接
31 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15", "root", "sorry");32 //System.out.println(conn.getClass().getName()); 要想知道具体类型,就这么办33 //3、创建代表SQL语句的对象
34 Statement stmt =conn.createStatement();35 //4、执行SQL语句
36 ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from users");37 //5、如果是查询语句,需要遍历结果集
38 while(rs.next()){39 System.out.println("---------------------");40 System.out.println(rs.getObject("id"));41 System.out.println(rs.getObject("name"));42 System.out.println(rs.getObject("password"));43 System.out.println(rs.getObject("email"));44 System.out.println(rs.getObject("birthday"));45 }46 //6、释放占用的资源
47 rs.close();48 stmt.close();49 conn.close();50 }51
52 }
3、运行代码,打印结果:
成功连接, 并读取了数据库中的数据!!!
四、DriverManager详解
DriverManager作用:
a、注册驱动:
方式一:(不建议使用)
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
原因:1、依赖具体驱动。2、导致驱动注册2遍
方式二:(建议)
Class.forName("com.mysql.jdbc.Driver");
b、获取与数据库的链接
url:SUN和数据库厂商间的协议。具体查阅数据库的文档。
public static Connection getConnection(String url,String user,String password) throws SQLException
public static Connection getConnection(String url,Properties info) throws SQLException
public static Connection getConnection(String url) throws SQLException
Connection作用:
所有的数据库操作都是基于链接之上的。
Statement createStatement():创建向数据库发送sql的statement对象。
所以代码修改为:
1 packagecom.bigshow1949.jdbc;2
3 importjava.sql.Connection;4 importjava.sql.DriverManager;5 importjava.sql.ResultSet;6 importjava.sql.SQLException;7 importjava.sql.Statement;8 importjava.util.Properties;9
10 //DriverManager详解
11 public classJdbcDemo2 {12
13 public static void main(String[] args) throwsException {14 //方式一:不建议使用15 //DriverManager.registerDriver(new com.mysql.jdbc.Driver());16 //方式二:
17 Class.forName("com.mysql.jdbc.Driver");18
19 //---------------------------------------------------------------------------------------20 //2、获取与数据库的链接21 //方式一:22 //Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15", "root", "sorry");23
24 //方式二:25 //Properties props = new Properties();26 //props.setProperty("user", "root");//参数名:参考数据库的文档27 //props.setProperty("password", "sorry");28 //props.setProperty("useUnicode", "true");//编码有关的参数29 //props.setProperty("characterEncoding", "utf8");30 //Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15",props);31
32 //方式三
33 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15?user=root&password=sorry");34
35 //System.out.println(conn.getClass().getName()); 要想知道具体类型,就这么办36 //3、创建代表SQL语句的对象
37 Statement stmt =conn.createStatement();38 //4、执行SQL语句
39 ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from users");40 //5、如果是查询语句,需要遍历结果集
41 while(rs.next()){42 System.out.println("---------------------");43 System.out.println(rs.getObject("id"));44 System.out.println(rs.getObject("name"));45 System.out.println(rs.getObject("password"));46 System.out.println(rs.getObject("email"));47 System.out.println(rs.getObject("birthday"));48 }49 //6、释放占用的资源
50 rs.close();51 stmt.close();52 conn.close();53 }54
55 }
五、Statement详解
作用:代表SQL语句对象。可以向数据库发送任何的SQL语句
ResultSet executeQuery(String sql):sql一般都是查询语句
int executeUpdate(String sql):sql一般是DML语句。insert update delete。返回值,操作几条记录。
boolean execute(String sql):sql可以是任意的语句。返回值不是代表成功与否。如果是查询语句,就有结果集,返回true。没有返回结果集的,返回false。
代码展示:
1 packagecom.itheima.jdbc;2
3 importjava.sql.Connection;4 importjava.sql.DriverManager;5 importjava.sql.ResultSet;6 importjava.sql.SQLException;7 importjava.sql.Statement;8 importjava.util.Properties;9
10 //Statement详解
11 public classJdbcDemo3 {12
13 public static void main(String[] args) throwsException {14 Class.forName("com.mysql.jdbc.Driver");15 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15", "root", "sorry");16 //3、创建代表SQL语句的对象
17 Statement stmt =conn.createStatement();18
19 //int num = stmt.executeUpdate("update users set password=123");20 //System.out.println(num);21
22 //boolean b = stmt.execute("update users set password=111");23 //System.out.println(b);24
25 //boolean b = stmt.execute("select * from users");26 //System.out.println(b);27 //if(b){28 //ResultSet rs = stmt.getResultSet();29 //}
30
31 stmt.close();32 conn.close();33 }34
35 }
六、ResultSet详解
作用:封装了查询的结果集
boolean next():游标下移。返回值是有无记录
boolean previous():游标上移。
boolean absolute(int count):定位到指定的行。第一行是1。
void beforeFirst():移动游标到第一行的前面。
void afterLast():移动游标到最后一行的后面。
先创建一个User类,代码:
1 packagecom.bigshow1949.user;2
3 importjava.io.Serializable;4 importjava.util.Date;5 //实体Bean:Enity JavaBean6 //Java类型和数据库类型:对应关系。相对固定的
7 public class User implementsSerializable {8 private intid;9 privateString name;10 privateString password;11 privateString email;12 privateDate birthday;13 public intgetId() {14 returnid;15 }16 public void setId(intid) {17 this.id =id;18 }19 publicString getName() {20 returnname;21 }22 public voidsetName(String name) {23 this.name =name;24 }25 publicString getPassword() {26 returnpassword;27 }28 public voidsetPassword(String password) {29 this.password =password;30 }31 publicString getEmail() {32 returnemail;33 }34 public voidsetEmail(String email) {35 this.email =email;36 }37 publicDate getBirthday() {38 returnbirthday;39 }40 public voidsetBirthday(Date birthday) {41 this.birthday =birthday;42 }43 @Override44 publicString toString() {45 return "User [id=" + id + ", name=" + name + ", password=" +password46 + ", email=" + email + ", birthday=" + birthday + "]";47 }48
49 }
ResultSet代码展示:
1 packagecom.bigshow1949.jdbc;2
3 importjava.sql.Connection;4 importjava.sql.DriverManager;5 importjava.sql.ResultSet;6 importjava.sql.Statement;7 importjava.util.ArrayList;8 importjava.util.List;9
10 importcom.bigshow1949.user.User;11
12 //ResultSet详解
13 public classJdbcDemo4 {14
15 public static void main(String[] args) throwsException {16 Class.forName("com.mysql.jdbc.Driver");17 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15", "root", "sorry");18 Statement stmt =conn.createStatement();19 ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from users");20 //rs.afterLast();21 //boolean b = rs.previous();22 //if(b){23 //User user = new User();24 //user.setId(rs.getInt("id"));25 //user.setName(rs.getString("name"));26 //user.setPassword(rs.getString("password"));27 //user.setEmail(rs.getString("email"));28 //user.setBirthday(rs.getDate("birthday"));29 //System.out.println(user);30 //}else{31 //System.out.println("没有结果");32 //}33
34 //5、如果是查询语句,需要遍历结果集
35 List users = new ArrayList();36 while(rs.next()){37 System.out.println("---------------------");38 //System.out.println(rs.getObject("id"));//不考虑字段的类型39 //System.out.println(rs.getObject("name"));40 //System.out.println(rs.getObject("password"));41 //System.out.println(rs.getObject("email"));42 //System.out.println(rs.getObject("birthday"));43
44 //System.out.println(rs.getObject(1));//第一列。所用从1开始。 一般很少用,做JDBC有关的框架时用45 //System.out.println(rs.getObject(2));46 //System.out.println(rs.getObject(3));47 //System.out.println(rs.getObject(4));48 //System.out.println(rs.getObject(5));
49 User user = newUser();50 //user.setId((Integer)rs.getObject("id"));
51 user.setId(rs.getInt("id"));52 user.setName(rs.getString("name"));53 user.setPassword(rs.getString("password"));54 user.setEmail(rs.getString("email"));55 user.setBirthday(rs.getDate("birthday"));56
57 users.add(user);58
59 }60 rs.close();61 stmt.close();62 conn.close();63
64 for(User u:users)65 System.out.println(u);66 }67
68 }
七、释放资源&JDBC编码的代码模板
如果当程序运行到users.add(user);就抛出异常了,那么rs.close();stmt.close();conn.close();就不会运行了,资源就不会释放了,所以要加上finally,不论程序是否异常都会执行,在这里释放资源。如果程序运行到stmt = conn.createStatement();就抛出异常,那么rs的值仍然是null,所以在finally里要判断下是否为null。
1 packagecom.bigshow1949.jdbc;2
3 importjava.sql.Connection;4 importjava.sql.DriverManager;5 importjava.sql.ResultSet;6 importjava.sql.SQLException;7 importjava.sql.Statement;8 importjava.util.ArrayList;9 importjava.util.List;10
11 importcom.bigshow1949.user.User;12
13 //释放资源14 //JDBC编码的代码模板
15 public classJdbcDemo5 {16
17 public static voidmain(String[] args) {18 Connection conn = null;19 Statement stmt = null;20 ResultSet rs = null;21 try{22 Class.forName("com.mysql.jdbc.Driver");23 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/day15", "root", "sorry");24 stmt =conn.createStatement();25 rs = stmt.executeQuery("select id,name,password,email,birthday from users");26 List users = new ArrayList();27 while(rs.next()){28 User user = newUser();29 user.setId(rs.getInt("id"));30 user.setName(rs.getString("name"));31 user.setPassword(rs.getString("password"));32 user.setEmail(rs.getString("email"));33 user.setBirthday(rs.getDate("birthday"));34 users.add(user);35 }36
37 } catch(Exception e) {38 e.printStackTrace();39 }finally{40 if(rs!=null){41 try{42 rs.close();43 } catch(SQLException e) {44 e.printStackTrace();45 }46 rs = null;47 }48 if(stmt!=null){49 try{50 stmt.close();51 } catch(SQLException e) {52 e.printStackTrace();53 }54 stmt = null;55 }56 if(conn!=null){57 try{58 conn.close();59 } catch(SQLException e) {60 e.printStackTrace();61 }62 conn = null;63 }64 }65
66 }67
68 }
八、Jdbc工具类
我们发现每次增删改查,finally里跟注册里的代码都一样,所以这部分代码单独抽取出来,写一个工具类,所以就有了JdbcUtil工具类。
首先,新建配置文件:dbcfg.properties,把一些字符串变量写在配置文件里,然后程序直接读取配置文件
代码:
1 driverClass=com.mysql.jdbc.Driver2 url=jdbc:mysql://localhost:3306/day15
3 user=root4 password=sorry
JdbcUtil工具类代码:
1 packagecom.bigshow1949.util;2
3 importjava.io.IOException;4 importjava.io.InputStream;5 importjava.sql.Connection;6 importjava.sql.DriverManager;7 importjava.sql.ResultSet;8 importjava.sql.SQLException;9 importjava.sql.Statement;10 importjava.util.Properties;11
12 //工具类
13 public classJdbcUtil {14
15 private staticString driverClass;16 private staticString url;17 private staticString user;18 private staticString password;19
20 static{21 try{22 ClassLoader cl = JdbcUtil.class.getClassLoader();23 InputStream in = cl.getResourceAsStream("dbcfg.properties");24 Properties props = newProperties();25 props.load(in);26 driverClass = props.getProperty("driverClass");27 url = props.getProperty("url");28 user = props.getProperty("user");29 password = props.getProperty("password");30
31 Class.forName(driverClass);32 } catch(Exception e) {33 throw newExceptionInInitializerError(e);34 }35 }36
37
38 public static Connection getConnection() throwsException{39 Connection conn =DriverManager.getConnection(url,user, password);40 returnconn;41 }42 public static voidrelease(ResultSet rs,Statement stmt,Connection conn){43 if(rs!=null){44 try{45 rs.close();46 } catch(SQLException e) {47 e.printStackTrace();48 }49 rs = null;50 }51 if(stmt!=null){52 try{53 stmt.close();54 } catch(SQLException e) {55 e.printStackTrace();56 }57 stmt = null;58 }59 if(conn!=null){60 try{61 conn.close();62 } catch(SQLException e) {63 e.printStackTrace();64 }65 conn = null;66 }67 }68 }
注意:注册驱动 Class.forName(driverClass);不要写在连接里,不然每次连接都要注册一次驱动,没有必要,初始化一次就够了。
测试代码:
1 packagecom.bigshow1949.jdbc;2
3 importjava.sql.Connection;4 importjava.sql.ResultSet;5 importjava.sql.Statement;6
7 importorg.junit.Test;8
9 importcom.bigshow1949.util.JdbcUtil;10
11 public classJdbcDemo6 {12
13 @Test14 public voidtestAdd(){15 Connection conn = null;16 Statement stmt = null;17 ResultSet rs = null;18 try{19 conn =JdbcUtil.getConnection();20 stmt =conn.createStatement();21 stmt.executeUpdate("insert into users (name,password,email,birthday) values ('wangwu','123','wangwu@itcast.cn','2000-10-01')");22 }catch(Exception e){23 throw newRuntimeException(e);24 }finally{25 JdbcUtil.release(rs, stmt, conn);26 }27 }28 @Test29 public voidtestUpdate(){30 Connection conn = null;31 Statement stmt = null;32 ResultSet rs = null;33 try{34 conn =JdbcUtil.getConnection();35 stmt =conn.createStatement();36 stmt.executeUpdate("update users set password=111 where id=4");37 }catch(Exception e){38 throw newRuntimeException(e);39 }finally{40 JdbcUtil.release(rs, stmt, conn);41 }42 }43 @Test44 public voidtestDelete(){45 Connection conn = null;46 Statement stmt = null;47 ResultSet rs = null;48 try{49 conn =JdbcUtil.getConnection();50 stmt =conn.createStatement();51 stmt.executeUpdate("delete from users where id=1");52 }catch(Exception e){53 throw newRuntimeException(e);54 }finally{55 JdbcUtil.release(rs, stmt, conn);56 }57 }58 }
九、项目文件结构
项目代码下载:
https://github.com/BigShow1949/01-JDBC