JDBC增删改查功能的实现
1、包装JDBC
1.1 将加载驱动、获取数据库链接包装在JDBC类中
1.2 将关闭数据库资源包装在JDBC类中
1 import java.sql.Connection; 2 import java.sql.DriverManager; 3 import java.sql.PreparedStatement; 4 import java.sql.SQLException; 5 6 public class JDBC { 7 public Connection connect(){ 8 Connection connection = null; 9 try { 10 //1 加载驱动 11 Class.forName("com.mysql.jdbc.Driver"); 12 //2 获取数据库链接 13 connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test2","root","root"); 14 } catch (ClassNotFoundException e) { 15 e.printStackTrace(); 16 } catch (SQLException throwables) { 17 throwables.printStackTrace(); 18 } 19 return connection; 20 } 21 22 public void disConnect(PreparedStatement preparedStatement, Connection connection){ 23 //关闭数据库资源 24 if (preparedStatement != null) 25 { 26 try { 27 preparedStatement.close(); 28 } catch (SQLException throwables) { 29 throwables.printStackTrace(); 30 } 31 } 32 if (connection != null){ 33 try { 34 connection.close(); 35 } catch (SQLException throwables) { 36 throwables.printStackTrace(); 37 } 38 } 39 } 40 }
2、依次实现增、删、改、查功能
2.1 实现增加数据功能
实现次功能的时候没有使用JDBC的包装
1 import java.sql.Connection; 2 import java.sql.DriverManager; 3 import java.sql.PreparedStatement; 4 import java.sql.SQLException; 5 6 /* 7 * 增加数据练习 8 * */ 9 public class UserDao2 { 10 11 Connection connection = null; 12 PreparedStatement preparedStatement = null; 13 public void insertUser2(User user){ 14 try { 15 //1 注册驱动 16 Class.forName("com.mysql.jdbc.Driver"); 17 18 //2 获取数据库链接 19 connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test2","root","root"); 20 21 //3 定义一个sql容器，装在sql语句 22 String sql = "insert into t_user(user_name,passwd,name) values(?,?,?)"; 23 preparedStatement = connection.prepareStatement(sql); 24 25 //4 为sql语句中的？赋值 26 preparedStatement.setString(1,user.getUserName()); 27 preparedStatement.setString(2,user.getPassword()); 28 preparedStatement.setString(3,user.getName()); 29 30 //5 执行sql语句 31 preparedStatement.execute(); 32 } catch (ClassNotFoundException e) { 33 e.printStackTrace(); 34 } catch (SQLException throwables) { 35 throwables.printStackTrace(); 36 }finally { 37 if (preparedStatement != null) 38 { 39 try { 40 preparedStatement.close(); 41 } catch (SQLException throwables) { 42 throwables.printStackTrace(); 43 } 44 } 45 if (connection != null) 46 { 47 try { 48 connection.close(); 49 } catch (SQLException throwables) { 50 throwables.printStackTrace(); 51 } 52 } 53 } 54 } 55 }
2.2 实现删除数据功能
1 import java.sql.*; 2 3 /* 4 * 删除数据 5 * */ 6 public class UserDao3 { 7 8 Connection connection = null; 9 PreparedStatement preparedStatement = null; 10 public void deleteData(String userName){ 11 try { 12 //1 加载驱动 13 Class.forName("com.mysql.jdbc.Driver"); 14 15 //2 获取数据库链接 16 connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test2","root","root"); 17 18 //3 创建sql容器，盛放sql语句 19 String sql = "delete from t_user where user_name=?"; 20 preparedStatement = connection.prepareStatement(sql); 21 22 //4 将问号填上 23 preparedStatement.setString(1,userName); 24 25 //5 执行sql语句 26 preparedStatement.execute(); 27 } catch (ClassNotFoundException e) { 28 e.printStackTrace(); 29 } catch (SQLException throwables) { 30 throwables.printStackTrace(); 31 }finally { 32 if (preparedStatement != null) 33 { 34 try { 35 preparedStatement.close(); 36 } catch (SQLException throwables) { 37 throwables.printStackTrace(); 38 } 39 } 40 41 if (connection != null) 42 { 43 try { 44 connection.close(); 45 } catch (SQLException throwables) { 46 throwables.printStackTrace(); 47 } 48 } 49 } 50 } 51 }
2.3 实现修改数据功能
1 import java.sql.Connection; 2 import java.sql.PreparedStatement; 3 import java.sql.SQLException; 4 5 /* 6 * 修改数据元素 7 * */ 8 public class UserDao4 { 9 Connection connection = null; 10 PreparedStatement preparedStatement = null; 11 12 public boolean modifyData(String userName, String passwd, String newPasswd){ 13 JDBC jdbc = new JDBC(); 14 try { 15 //1 链接数据库 - 2 获取数据库链接 16 connection = jdbc.connect(); 17 18 //3 创建sql容器，并置放sql语句 19 String sql = "update t_user set passwd = ? where user_name = ? and passwd = ?"; 20 preparedStatement = connection.prepareStatement(sql); 21 22 //4 填充问号 23 preparedStatement.setString(1,newPasswd); 24 preparedStatement.setString(2,userName); 25 preparedStatement.setString(3,passwd); 26 27 //5 执行sql语句 28 preparedStatement.executeUpdate(); 29 } catch (SQLException throwables) { 30 throwables.printStackTrace(); 31 }finally { 32 jdbc.disConnect(preparedStatement,connection); 33 } 34 return true; 35 } 36 }
2.4 实现查询数据功能
1 /* 2 * 查询数据库数据 3 * */ 4 5 import java.sql.Connection; 6 import java.sql.PreparedStatement; 7 import java.sql.ResultSet; 8 import java.sql.SQLException; 9 10 public class UserDao5 { 11 12 Connection connection = null; 13 PreparedStatement preparedStatement = null; 14 ResultSet resultSet = null; 15 16 public void selectData(String userName, String passwd){ 17 JDBC jdbc = new JDBC(); 18 try { 19 //1 加载驱动 2 获取数据库链接 20 Class.forName("com.mysql.jdbc.Driver"); 21 connection = jdbc.connect(); 22 23 //3 创建sql容器，加载sql语句 24 String sql = "select * from t_user where user_name=? and passwd=?"; 25 preparedStatement = connection.prepareStatement(sql); 26 27 //4 填充问号 28 preparedStatement.setString(1,userName); 29 preparedStatement.setString(2,passwd); 30 31 //5 执行sql语句 32 resultSet = preparedStatement.executeQuery(); 33 34 //6 处理查询结果 35 while (resultSet.next()){ 36 int id = resultSet.getInt("id"); 37 String user_Name = resultSet.getString("user_name"); 38 String password = resultSet.getString("passwd"); 39 String name = resultSet.getString("name"); 40 System.out.println(id+" "+user_Name+" "+password+" "+name); 41 } 42 43 } catch (ClassNotFoundException e) { 44 e.printStackTrace(); 45 } catch (SQLException throwables) { 46 throwables.printStackTrace(); 47 }finally { 48 //7 关闭数据库资源 49 if (resultSet != null) 50 { 51 try { 52 resultSet.close(); 53 } catch (SQLException throwables) { 54 throwables.printStackTrace(); 55 } 56 } 57 jdbc.disConnect(preparedStatement,connection); 58 } 59 } 60 61 }
总结：
JDBC的操作步骤：
一、执行静态sql语句
1、加载驱动
两种方法：
A、Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver);
B、Class.forName("com.mysql.jdbc.Driver");
2、获取数据库链接
Connection connection = DriverManager.getConnection(String url, String user, String passwd;
3、获取数据库操作对象
Statement statement = connection.createStatement();
4、执行sql语句
String sql = DQL
ResultSet resultSet = statement.executeQuery(sql);
String sql = DML //数据操纵
int i = statement.executeUpdate(sql);
5、如果是查询语句，则处理结果
while(resultSet.next())
{
int id = resultSet.getId("id");
}
6、关闭数据库资源
if(resultSet != null)
resultSet.close();
if(statement != null)
statement.close();
if(connection != null)
connection.close();
二、执行动态sql语句
1、加载驱动
2、获取数据库连接
3、创建sql容器，加载sql语句
String sql = "insert into table_name(id,name) values(?,?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
4、填充？
preparedStatement.setInt(1, user.getId());
preparedStatement.setsTring(1, user.getName());
5、执行sql语句
preparedStatement.execute();
6、关闭数据库资源
preparedStatement.close();
connection.close();
浙公网安备 33010602011771号