JDBC学习笔记

时间：2022-03-14 19:40

01.创建数据库

创建数据库 jsp_db

创建表 tbl_user

1 CREATE TABLE tbl_user (
2     id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
3     name VARCHAR(50) NOT NULL DEFAULT ‘‘,
4     password VARCHAR(50) NOT NULL DEFAULT ‘‘,
5     email VARCHAR(50) DEFAULT ‘‘,
6     PRIMARY KEY (id)
7 )  ENGINE=INNODB DEFAULT CHARSET=UTF8;

创建表 tbl_address

1 CREATE TABLE tbl_address (
2     id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
3     city VARCHAR(20) DEFAULT NULL,
4     country VARCHAR(20) DEFAULT NULL,
5     user_id INT(11) UNSIGNED NOT NULL,
6     PRIMARY KEY (id)
7 )  ENGINE=INNODB DEFAULT CHARSET=UTF8;

向表中添加数据

1 insert into tbl_user(id,name,password,email)
2 values
3 (1,‘buddha‘,‘123456‘,‘buddha@163.com‘),
4 (2,‘shakyamuni‘,‘123456‘,‘shakyamuni@163.com‘);
5 insert into tbl_address(city,country,user_id)
6 values (‘didu‘,‘tiandu‘,1);
7 insert into tbl_address(city,country,user_id)
8 values (‘modu‘,‘tiandu‘,2);

02.数据查询

在eclipse的项目中添加mysql的jar包。

mysql-connector-java-5.1.7-bin.jar 此包放入项目WebContent-WEB-INF-lib中，项目右键选择Build Path-Java Build Path - Libraries, Add JARs...找到项目中在上一步引入的jar包，OK JDBC程序编写步骤：加载驱动-打开链接-执行查询-处理结果-清理环境 Java Resources -src 中创建CLSS， Package:jdbc Name:JDBCTest 完整代码：

 1 package jdbc;//包名
 2 import java.sql.Connection; //连接
 3 import java.sql.DriverManager;//驱动程序管理器
 4 import java.sql.ResultSet;//结果集
 5 import java.sql.Statement;//？
 6 //Statement 是 Java 执行数据库操作的一个重要方法，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句。Statement对象，用于执行不带参数的简单SQL语句。
 7 public class JDBCTest {
 8     public static void main(String[] args) {
 9         
10         String sql ="SELECT * FROM tbl_user"; //查询 tbl_user表
11         Connection conn =null;//当前的数据库连接
12         Statement  st =null;//用于向数据库发送sql语句
13         ResultSet rs =null;//结果集，封装了从数据库中查询到的数据
14         
15         try {
16             //注册JDBC的驱动
17             Class.forName("com.mysql.jdbc.Driver");
18             //连接数据库 （连接地址,帐号，密码）
19             conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db","root","851111");
20             st = conn.createStatement();// 实例化Statement对象
21             rs=st.executeQuery(sql);// 执行给定的SQL语句，该语句返回单个ResultSet【结果集】对象。
22             
23             //输出查到的数据，注意相应的数据类型
24             while(rs.next()){
25                 System.out.print(rs.getInt("id")+" ");
26                 System.out.print(rs.getString("name")+" ");
27                 System.out.print(rs.getString("password")+" ");
28                 System.out.print(rs.getString("email")+" ");
29                 System.out.println();
30             }
31             
32         } catch (Exception e) {
33             e.printStackTrace();
34         //资源清理的逻辑
35         }finally {
36             
37             try {
38                 rs.close();//关闭结果集
39             } catch (Exception e2) {
40             }
41             try {
42                 st.close();//关闭？？
43             } catch (Exception e3) {
44             }
45             try {
46                 conn.close();//关闭数据库连接
47             } catch (Exception e4) {    
48             }
49         }
50     }
51 }

右键Run As - Java Application 控制台输出结果图：技术分享

03.数据添加，更新，删除 完整代码：

 1 package jdbc;//包名
 2 import java.sql.Connection; //连接
 3 import java.sql.DriverManager;//驱动程序管理器
 4 import java.sql.ResultSet;//结果集
 5 import java.sql.Statement;//？
 6 //Statement 是 Java 执行数据库操作的一个重要方法，用于在已经建立数据库连接的基础上，向数据库发送要执行的SQL语句。Statement对象，用于执行不带参数的简单SQL语句。
 7 public class JDBCTest {
 8     public static Connection getConnection(){
 9         Connection conn =null;
10         try {
11             //加载JDBC的驱动
12             Class.forName("com.mysql.jdbc.Driver");
13             //连接数据库 （连接地址,帐号，密码）
14             conn =DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db","root","851111");
15             
16         } catch (Exception e) {
17             e.printStackTrace();
18         }
19         return conn;
20     }
21     //insert【添加】
22     public static void insert(){
23         Connection conn=getConnection();
24         try {
25             //向数据库表中添加一条数据
26             String sql ="INSERT INTO tbl_user(name,password,email)"+"VALUES(‘dibao‘,‘123456‘,‘dibao@163.com‘)";
27             Statement st =conn.createStatement();
28             int count =st.executeUpdate(sql);//执行给定SQL语句，返回值是更新的记录数量
29             System.out.println("向用户表中插入了"+count+"条记录");
30             conn.close();
31         } catch (Exception e) {
32             e.printStackTrace();
33         }
34     }
35     //update【更新】
36     public static void update(){
37         Connection conn=getConnection();
38         try {
39             //将name=dibao的邮箱改为：dibao@126.com
40             String sql ="UPDATE tbl_user SET email=‘dibao@126.com‘ WHERE name = ‘dibao‘";
41             Statement st =conn.createStatement();
42             int count =st.executeUpdate(sql);//执行给定SQL语句，返回值是更新的记录数量
43             System.out.println("向用户表中更新了"+count+"条记录");
44             conn.close();
45         } catch (Exception e) {
46             e.printStackTrace();
47         }
48     }
49     
50     //delete【删除】
51     public static void delete(){
52         Connection conn =getConnection();
53         try {
54             //将name=shakyamuni的数据删掉。
55             String sql ="DELETE FROM tbl_user WHERE name=‘shakyamuni‘";
56             Statement st =conn.createStatement();
57             int count =st.executeUpdate(sql);//执行给定SQL语句，返回值是更新的记录数量
58             System.out.println("向用户表中删除了"+count+"条记录");
59             conn.close();
60         } catch (Exception e) {
61             e.printStackTrace();
62         }
63     }
64     
65     public static void main(String[] args) {
66         //insert();//调用添加方法
67         //update();//调用更新方法
68         delete();//调用删除方法
69     }
70 }

0.4事务处理

事务的语句：

开始事务：BEGIN TRANSACTION 提交事务：COMMIT TRANSACTION 回滚事务：ROLLBACK TRANSACTION 事务回滚：当发生异常时，使用事务回滚，则不会添加任何数据。 Transaction.java 完整代码：

 1 package jdbc;
 2 import java.sql.Connection;
 3 import java.sql.DriverManager;
 4 import java.sql.SQLException;
 5 import java.sql.Statement;
 6 public class TransactionTest {
 7     public static Connection getConnection() {
 8         Connection conn = null;
 9         try {
10             Class.forName("com.mysql.jdbc.Driver");
11             conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "851111");
12         } catch (Exception e) {
13             e.printStackTrace();
14         }
15         return conn;
16     }
17     //添加用户表
18     public static void insertUserData(Connection conn) throws SQLException {
19         String sql = "INSERT INTO tbl_user(id,name,password,email)"
20                 + "VALUES(10,‘shakyamuni‘,‘123456‘,‘shakyamuni@163.com‘)";
21         Statement st = conn.createStatement();
22         int count = st.executeUpdate(sql);
23         System.out.println("向用户表插入了" + count + "条记录");
24     }
25     
26     //添加地址表
27     public static void insertAddressData(Connection conn) throws SQLException {
28         String sql = "INSERT INTO tbl_address(id,city,country,user_id)" + "VALUES(1,‘gudu‘,‘tiandu‘,‘10‘)";
29         Statement st = conn.createStatement();
30         int count = st.executeUpdate(sql);
31         System.out.println("向地址表插入了" + count + "条记录");
32     }
33     
34     //程序主方法
35     public static void main(String[] args) {
36         Connection conn =null;
37         try {
38             conn=getConnection();
39             conn.setAutoCommit(false);//禁止事务自动提交
40             insertUserData(conn);
41             insertAddressData(conn);
42             
43             conn.commit(); //提交事务
44         } catch (SQLException e) {
45             System.out.println("==捕获到SQL异常==");
46             e.printStackTrace();
47             
48             try {
49                 conn.rollback(); //事务回滚
50                 System.out.println("事务回滚成功");
51             } catch (Exception e2) {
52                 e2.printStackTrace();
53             }
54         }finally {//资源清理相关代码
55             try {
56                 if(conn!=null){
57                     conn.close();
58                 }
59             } catch (Exception e3) {
60                 e3.printStackTrace();
61             }
62         }
63     }
64 }

05.程序优化

Java Resources -src 创建dbconfig.properties文件

代码：｛注：地址无需加\｝

1 driver=com.mysql.jdbc.Driver    //加载驱动器
2 dburl=jdbc:mysql://localhost:3306/jsp_db  //数据库连接
3 user=root    //用户名
4 password=851111  //密码

Java Resources -src 创建数据库链接工厂类 Package:util Name:ConnectionFactory 完整代码：

 1 package util;
 2 import java.io.InputStream;
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.util.Properties;
 6 public class ConnectionFactory {
 7     
 8     private static String driver;
 9     private static String dburl;
10     private static String user;
11     private static String password; 
12     
13     private static final ConnectionFactory factory =new ConnectionFactory();
14     
15     private Connection conn;
16     
17 //配置信息的读取代码
18     static {
19         Properties prop =new Properties();//定义类，处理键值对
20         try {
21             //获取配置文件【dbconfig.properties】
22 //获取属性文件中的内容，首先获取类加载器，然后读取属性文件中的内容，从输入流中读取键值对列表
23             InputStream in =ConnectionFactory.class.getClassLoader().getResourceAsStream("dbconfig.properties");
24             prop.load(in);
25         } catch (Exception e) {
26             System.out.println("===配置文件读取错误===");
27         }
28         driver =prop.getProperty("driver");
29         dburl =prop.getProperty("dburl");
30         user =prop.getProperty("user");
31         password =prop.getProperty("password");
32     }
33     
34 //默认的构造函数
35     private ConnectionFactory(){
36         
37     }
38     
39     public static ConnectionFactory getInstance(){
40         return factory;
41     }
42     
43 //获取数据库链接的方法
44     public Connection makeConnection(){
45         try {
46             Class.forName(driver);
47             conn =DriverManager.getConnection(dburl,user,password);
48         } catch (Exception e) {
49             e.printStackTrace();
50         }
51         return conn;
52     }
53 }

Java Resources -src 创建测试类 Packahe:test Name:ConnectionFactoryTest 完整代码：

 1 package test;
 2 import java.sql.Connection;
 3 import util.ConnectionFactory;
 4 public class ConnectionFactoryTest {
 5    //throws Exception 异常抛出
 6     public static void main(String[] args) throws Exception{
 7 //获取实例
 8         ConnectionFactory cf = ConnectionFactory.getInstance();
 9         //获取数据库链接
10         Connection conn =cf.makeConnection();
11         //打印conn.getAutoCommit()的属性
12         System.out.println(conn.getAutoCommit());
13     }
14 }

右键Run As - Java Application 控制台输出结果图：技术分享

Java Resources - src 创建实体类 Package:entity Name:IdEntity 代码：

 1 package entity;//实体类
 2 //abstract【抽象】
 3 public abstract class IdEntity {
 4     
 5     protected Long id;
 6     
 7     public Long getId(){
 8         return id;
 9     }
10     
11     public void setId(Long id){
12         this.id=id;
13     }
14 }

entity包下创建user类 Name:User Browse...输入IdEntity选中，声明字段之后，Source - Generate Getters Setters...【创建get,set方法】 Source - Generte toString()...【】除了原有勾选，添加 Inherited fields - id 勾选。代码：

 1 package entity;//实体类
 2 // User 继承了Id实体类
 3 public class User extends IdEntity {
 4     
 5     private String name;
 6     private String password;
 7     private String email;
 8     public String getName() {
 9         return name;
10     }
11     public void setName(String name) {
12         this.name = name;
13     }
14     public String getPassword() {
15         return password;
16     }
17     public void setPassword(String password) {
18         this.password = password;
19     }
20     public String getEmail() {
21         return email;
22     }
23     public void setEmail(String email) {
24         this.email = email;
25     }
26     @Override
27     public String toString() {
28         return "User [name=" + name + ", password=" + password + ", email=" + email + ", id=" + id + "]";
29     }
30         
31 }

entity包下创建Address类 Name:Address 继承IdEntity ,其他与user基本相同代码：

 1 package entity;
 2 public class Address extends IdEntity {
 3     
 4     private String city;
 5     private String country;
 6     private Long userId;
 7     public String getCity() {
 8         return city;
 9     }
10     public void setCity(String city) {
11         this.city = city;
12     }
13     public String getCountry() {
14         return country;
15     }
16     public void setCountry(String country) {
17         this.country = country;
18     }
19     public Long getUserId() {
20         return userId;
21     }
22     public void setUserId(Long userId) {
23         this.userId = userId;
24     }
25     @Override
26     public String toString() {
27         return "Address [city=" + city + ", country=" + country + ", userId=" + userId + ", id=" + id + "]";
28     }    
29 }

Java Resources -src 创建接口 Package:dao Name:UserDao 代码：

 1 package dao;
 2 import java.sql.Connection;
 3 import java.sql.SQLException;
 4 import entity.User;
 5 //接口
 6 public interface UserDao {
 7     
 8     public void save(Connection conn ,User user )throws SQLException;
 9     
10     public void update(Connection conn,Long id,User user) throws SQLException;
11     
12     public void delete(Connection conn,User user) throws SQLException;
13 }

dao包下创建 Package:dao.impl 【接口具体实现类】 Name:UserDaoImpl Add... UserDao 代码：

 1 package dao.impl; //接口具体实现类
 2 import java.sql.Connection;
 3 import java.sql.SQLException;
 4 import java.sql.PreparedStatement;//这里引入的包注意更改
 5 import dao.UserDao;
 6 import entity.User;
 7 public class UserDaoImpl implements UserDao {
 8     //保存用户信息
 9     
10     @Override
11     public void save(Connection conn, User user) throws SQLException {
12         PreparedStatement ps =conn.prepareCall("INSERT INTO tbl_user(name,password,email) VALUES(?,?,?)");
13         
14         ps.setString(1, user.getName());
15         ps.setString(2,user.getPassword());
16         ps.setString(3,user.getEmail());
17         ps.execute();
18     }
19     
20     //根据用户指定的ID更新用户信息
21     
22     @Override
23     public void update(Connection conn, Long id, User user) throws SQLException {
24         String updateSql="UPDATE tbl_user SET name = ?,password = ?,email = ? WHERE id = ?";
25         PreparedStatement ps =conn.prepareStatement(updateSql);
26         
27         ps.setString(1, user.getName());
28         ps.setString(2, user.getPassword());
29         ps.setString(3, user.getEmail());
30         ps.setLong(4, id);
31         ps.execute();
32     }
33     
34     //删除指定的用户信息
35     
36     @Override
37     public void delete(Connection conn, User user) throws SQLException {
38         PreparedStatement ps =conn.prepareStatement("DELETE FROM tbl_user WHERE id = ?");
39         ps.setLong(1, user.getId());
40         ps.execute();
41     }
42 }

Java Resources -src 创建类 Packages:test Name:UserDaoTest 勾选：public static void main(String[] args) 代码：

 1 package test;
 2 import java.sql.Connection;
 3 import dao.UserDao;
 4 import dao.impl.UserDaoImpl;
 5 import entity.User;
 6 import util.ConnectionFactory;//注意此包的引入
 7 public class UserDaoTest {
 8     public static void main(String[] args) {
 9         Connection conn =null;
10         try {
11             conn=ConnectionFactory.getInstance().makeConnection();
12             conn.setAutoCommit(false);
13             
14             UserDao userDao =new UserDaoImpl();
15             User dibao =new User();
16             dibao.setName("dibao");
17             dibao.setPassword("123456");
18             dibao.setEmail("dibao@163.com");
19             
20             userDao .save(conn, dibao);
21             
22             conn.commit();
23             
24         } catch (Exception e) {
25             try {
26                 conn.rollback();
27             } catch (Exception e2) {
28                 e2.printStackTrace();
29             }
30         }
31     }
32 }