一:定义:
jdbc: Java Database Connectiontivity 数据库连接技术
通俗点说,在java代码中,使用JDBC提供的方法,可以发送字符串类型的sql语句到数据库管理软件(mysql,Oracle)并获取执行语句结果,进而实现数据库数据curd操作的技术
二:核心api
》DriverManager:1)根据第三方厂商提供的驱动jar包注册到程序中2)根据驱动程序获取数据库连接
》Connection :是接口,和数据库建立的连接对象,可以多次执行curd操作,并可以获取Statement,PreparedStatement,CallalbeStatement, 其中PreparedStatement为重点
》PesultSet :接口, 为返回的查询的结果集,并包含一个指针
三:实例
1.Statement使用方式(局限:只能执行固定的sql语句 属于硬编码)
public class StatementTest { public static void main(String[] args) throws Exception { //1.注册驱动 方式一: //DriverManager.registerDriver(new Driver()); //方式二 :反射 ;Class.forName("com.mysql.cj.jdbc.Driver"); Class.forName("com.mysql.cj.jdbc.Driver"); //2.建立连接 :getConnection()三个参数 Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1", "root", "root"); // getConnection()二个参数 // Properties pro =new Properties(); // pro.put("user","root"); // pro.put("password","root"); // Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1", pro); //getConnection()1个参数 //Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?user=root&password=root"); //3.创建statement对象 Statement statement = connection.createStatement(); //4.发送sql,并返回结果集 String sql="select id ,name, salary from emp "; ResultSet resultSet = statement.executeQuery(sql); //5.解析结果集, while(resultSet.next()){ int id = resultSet.getInt("id"); // 也可以调用列的索引,重左向右,从1开始 //int id = resultSet.getInt(1); String name = resultSet.getString("name"); double salary = resultSet.getDouble("salary"); System.out.println(id +"-"+name+"-"+ salary); } //6.关闭资源 resultSet.close(); statement.close(); connection.close(); }
2.PreparedStatement :预定义的方式,开发主要使用该种方法
public class PreparedStatementTest1 { public static void main(String[] args) throws Exception { //实现从键盘登录系统并判断是否登录成功 Scanner scan =new Scanner(System.in); System.out.println("请输入用户名:"); String user = scan.nextLine(); System.out.println("请输入密码:"); String password = scan.nextLine(); //1.注册驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2.创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test1", "root", "root"); //3.创建PreparedStatement对象 String sql="select name,password from user where name=? and password=?"; PreparedStatement pres= connection.prepareStatement(sql); //4.占位符赋值 pres.setObject(1,user); pres.setObject(2,password); //5.发送sql并返回结果集 ResultSet resultSet = pres.executeQuery(); if(resultSet.next()){ System.out.println("登录成功!"); }else{ System.out.println("登录失败!"); } //6.关闭资源 resultSet.close(); pres.close(); connection.close(); } }
3.PreparedStatement 的curd的操作
public class PreparedStatementTest2 { @Test //新增一条记录 public void insertTest() throws Exception { //1.注册驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2.创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test1", "root", "root"); //3.创建PreparedStatement对象 String sql = "insert into user (id,name,password)values(?,?,?)"; PreparedStatement pres = connection.prepareStatement(sql); //4.占位符赋值 pres.setObject(1, 2); pres.setObject(2, "jerry"); pres.setObject(3, 111111); //5.发送sql语句并返回执行结果,执行结果为影响的行数 int rows = pres.executeUpdate(); System.out.println(rows); //6.关闭资源 pres.close(); connection.close(); } @Test //修改记录 public void updateTest() throws Exception { //1.注册驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2.创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test1", "root", "root"); //3.创建PreparedStatement对象 String sql = "update user set password=? where id=?"; PreparedStatement pres = connection.prepareStatement(sql); //4.占位符赋值 pres.setObject(1, "222222"); pres.setObject(2, 3); //5.发送sql语句并返回执行结果,执行结果为影响的行数 int rows = pres.executeUpdate(); System.out.println(rows); //6.关闭资源 pres.close(); connection.close(); } @Test //删除记录 public void deleteTest() throws Exception { //1.注册驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2.创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test1", "root", "root"); //3.创建PreparedStatement对象 String sql = "delete from user where id=?"; PreparedStatement pres = connection.prepareStatement(sql); //4.占位符赋值 pres.setObject(1, 2); //5.发送sql语句并返回执行结果,执行结果为影响的行数 int rows = pres.executeUpdate(); System.out.println(rows); //6.关闭资源 pres.close(); connection.close(); } @Test //查询记录 public void queryTest() throws Exception { //1.注册驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2.创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test1", "root", "root"); //3.创建PreparedStatement对象 String sql = "select id,name,password from user where id=?"; PreparedStatement pres = connection.prepareStatement(sql); //4.占位符赋值 pres.setObject(1, 3); //5.发送sql语句并返回执行结果 ResultSet resultSet = pres.executeQuery(); while(resultSet.next()){ int id = resultSet.getInt(1); String name = resultSet.getString(2); String password = resultSet.getString(3); System.out.println(id+","+name+","+password); } //6.关闭资源 resultSet.close(); pres.close(); connection.close(); } }
4.使用PreparedStatement 实现查询数据,并把结果集信息存到List<Map>中
public class PreparedStatementTest3 { public static void main(String[] args) throws Exception { //实现查询数据,并把结果集信息存到List<Map>中 //1.注册驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2.创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test1", "root", "root"); //3.编写sql语句 String sql = "select id ,name ,password as ps from user where id<?"; //4.创建PreparedStaement 对象 PreparedStatement preparedStatement = connection.prepareStatement(sql); //5.占位符赋值 preparedStatement.setObject(1,100); //6.发送sql语句,并返回结果集 ResultSet resultSet = preparedStatement.executeQuery(); //7.解析结果集并封装到List中 List<Map> list =new ArrayList<>(); //获取列的数据集(元数据) ResultSetMetaData metaData = resultSet.getMetaData(); //获取列的总数 int columnCount = metaData.getColumnCount(); //方式一:该种方式不好,如果列有别名解析不出来 while(resultSet.next()){ // Map map =new HashMap(); // int id = resultSet.getInt("id"); // String name = resultSet.getString("name"); // String password = resultSet.getString("password"); // map.put("id",id); // map.put("name",name); // map.put("password",password); // list.add(map); //每一行创建一个map Map map =new HashMap(); for(int i=1;i<=columnCount;i++){ Object value = resultSet.getObject(i); //获取指定列的标签,可以获取别名 String columnLabel = metaData.getColumnLabel(i); //把每一列的值存到map中 map.put(columnLabel,value); } list.add(map); } System.out.println(list); //8.关闭资源 resultSet.close(); preparedStatement.close(); connection.close(); }
5.使用PreparedStatement 获取自增长主键
public class PreparedStatementTest4 { public static void main(String[] args) throws Exception { //获取自增长主键值 // 前置条件:创建自增主键表并导入数据 // create table user1 // (id int primary key auto_increment, // name varchar(20), // password varchar(20)); // insert into user1(name,password)values('tom',123456),('kate','111111'); //1.注册驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2.创建连接 Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test1", "root", "root"); //3.创建sql语句 String sql ="insert into user1(name,password)values(?,?)"; //4.创建PreparedStament对象,使用带返回主键的参数 PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); //5.占位符赋值 preparedStatement.setObject(1,"jerry"); preparedStatement.setObject(2,"666666"); //6.发送sql语句并返回结果集 int rows = preparedStatement.executeUpdate(); if(rows>0){ System.out.println("插入数据成功"); //7.获取主键结果集 ResultSet generatedKeys = preparedStatement.getGeneratedKeys(); //移动光标 generatedKeys.next(); int key = generatedKeys.getInt(1); System.out.println("输入数据的主键为:"+key); }else{ System.out.println("插入数据失败"); } //8.关闭资源 preparedStatement.close(); connection.close(); } }
6.数据库的批量操作
public class PreparedStatementTest5 { @Test public void BatchTest1() throws Exception { //1.原始方法批量添加,并计算耗时 //1.注册驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2.获取连接 Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test1", "root", "root"); //3.执行sql String sql = "insert into user1(name,password)values(?,?)"; //4.获取PreparedStatement 对象 PreparedStatement preparedStatement = connection.prepareStatement(sql); //5.占位符赋值,并计算耗时 long start = System.currentTimeMillis(); for(int i=0;i<=10000;i++){ preparedStatement.setObject(1,"test0"+i); preparedStatement.setObject(2,"111111"+i); preparedStatement.executeUpdate(); } long end = System.currentTimeMillis(); //总耗时为:75997ms System.out.println("总耗时为:"+(end-start)+"ms"); //6.关闭资源 preparedStatement.close(); connection.close(); } @Test public void BatchTest2() throws Exception { //2.批量添加,并计算耗时 //1.注册驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //2.获取连接 Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test1", "root", "root"); //3.执行sql String sql = "insert into user1(name,password)values(?,?)"; //4.获取PreparedStatement 对象 PreparedStatement preparedStatement = connection.prepareStatement(sql); //5.占位符赋值,并计算耗时 long start = System.currentTimeMillis(); for(int i=0;i<=10000;i++){ preparedStatement.setObject(1,"test0"+i); preparedStatement.setObject(2,"111111"+i); preparedStatement.addBatch();//只添加到vauls后面,不执行 } //批量执行 preparedStatement.executeBatch(); long end = System.currentTimeMillis(); //总耗时为:76953ms System.out.println("总耗时为:"+(end-start)+"ms"); //6.关闭资源 preparedStatement.close(); connection.close(); } }
7.数据库的事务操作
public class BankDAO { // 数据库数据准备sql: // create table t_bank // (id int primary key auto_increment, // account varchar(20)comment '账号', // money int unsigned comment'余额不能小于0') // // insert into t_bank (account,money)values('wangdacui',1000),('ergouzhi',1000); //加钱 public void add(Connection connection,String addaccount, int money) throws Exception { //创建sql String sql = "update t_bank set money=money+? where account=?"; //创建PreparedStatement PreparedStatement preparedStatement = connection.prepareStatement(sql); //占位符赋值 preparedStatement.setObject(1,money); preparedStatement.setObject(2,addaccount); //发送sql语句 preparedStatement.executeUpdate(); System.out.println("加钱成功"); //7.关闭连接 preparedStatement.close(); } //减钱 public void sub(Connection connection,String subaccount ,int money) throws Exception { //创建sql String sql = "update t_bank set money=money-? where account=?"; //创建PreparedStatement PreparedStatement preparedStatement = connection.prepareStatement(sql); //占位符赋值 preparedStatement.setObject(1,money); preparedStatement.setObject(2,subaccount); //发送sql语句 preparedStatement.executeUpdate(); System.out.println("减钱成功"); //7.关闭连接 preparedStatement.close(); } } public class BankService { @Test public void testTransfer() throws Exception { transfer("wangdacui","ergouzhi",500); } public void transfer(String addaccount,String subaccount,int money) throws Exception { BankDAO bankdao =new BankDAO(); Connection connection = JdbcUtil2.getConnection(); try { //关闭事务自动提交 connection.setAutoCommit(false); //数据库操作,加钱 bankdao.add(connection, addaccount, money); System.out.println("__________________"); //数据库操作,减钱 bankdao.sub(connection, subaccount, money); //事务提交 connection.commit(); }catch (Exception e){ //事务回滚 connection.rollback(); //抛出异常 throw e; }finally { JdbcUtil2.freeConnection(); } } }
8.数据库连接池的使用(druid 为阿里产品)
配置文件:为src下的druid.properties
配置内容:
driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/test1 username=root password=root
@Test public void testsoft() throws Exception { Properties pro =new Properties(); //类的加载器读取文件 InputStream ips = DruitTest1.class.getClassLoader().getResourceAsStream("druid.properties"); // pro.load(ips); //获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(pro); //获取连接 Connection connection = dataSource.getConnection(); System.out.println(connection); //数据库crud //回收连接 connection.close(); } }
9.封装数据库工具类
public class JdbcUtil2 { private static DataSource dataSource; private static ThreadLocal<Connection> threadLocal =new ThreadLocal<Connection>(); //静态代码块 实例化datasource static { Properties pro=new Properties(); InputStream input= JdbcUtil2.class.getClassLoader().getResourceAsStream("druid.properties"); try { pro.load(input); } catch (IOException e) { e.printStackTrace(); } try { dataSource = DruidDataSourceFactory.createDataSource(pro); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException { //从共享线程变量获取连接 Connection connection = threadLocal.get(); if(connection==null){ connection=dataSource.getConnection(); threadLocal.set(connection); } return connection; } public static void freeConnection() throws SQLException { Connection connection = threadLocal.get(); if(connection!=null){ threadLocal.remove(); connection.setAutoCommit(true); connection.close();//回收到线程池 } } }
10.工具类封装数据库操作
public abstract class BaseDao { public int exeupdate(String sql,Object ... params) throws SQLException { //获取连接 Connection connection = JdbcUtil2.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql); int length = params.length; //可变形参可以当成数组使用,遍历赋值 for(int i=0;i<length;i++){ preparedStatement.setObject(i+1,params[i]); } int row = preparedStatement.executeUpdate(); //根据是否开启事务,决定是否关闭连接 if(connection.getAutoCommit()){ JdbcUtil2.freeConnection(); } return row; } //<T>:返回的类型不确认,所以定义为泛型 //:Class<T>clazz, 传入clazz 类型 一是确定T类型,然后为了调用反射给属性赋值 public <T> List<T> executeQuery(Class<T>clazz, String sql , Object ... params) throws Exception { //获取连接 Connection connection = JdbcUtil2.getConnection(); //创建preparedStatement对象 PreparedStatement preparedStatement = connection.prepareStatement(sql); //占位符赋值 for(int i=1;i<=params.length;i++){ preparedStatement.setObject(i,params[i-1]); } //发送数据并返回结果集 ResultSet resultSet = preparedStatement.executeQuery(); //获取列的元数据集 ResultSetMetaData metaData = resultSet.getMetaData(); //获取结果集的列数 int columnCount = metaData.getColumnCount(); List<T> list =new ArrayList<>(); while(resultSet.next()){ //通过反射,获取对象 T t = clazz.newInstance(); for(int i=1;i<columnCount;i++){ //获取列的值 Object object = resultSet.getObject(i); //获取列名或者别名 String columnLabel = metaData.getColumnLabel(i); //通过反射获取 对象的指定属性 Field declaredField = clazz.getDeclaredField(columnLabel); //设置属性可访问 declaredField.setAccessible(true); //给属性赋值 declaredField.set(t,object); } list.add(t); } //关闭资源 resultSet.close(); preparedStatement.close(); //未开启事务 if(connection.getAutoCommit()){ JdbcUtil2.freeConnection(); } return list; } }
四:综合练习:实现客户管理的数据库后台操作
主要结构:
javabean: Customer类
service: CustomerService 类:业务层的类
Dao: 数据库操作类:BaseDao,CustomerDao
Util: JdbcUtil2:数据库工具类
main:CustomerManage :测试类
1.Customer 类代码:
public class Customer { private int id; private String name; private String gender; private int age; private double salary; private String phone ; public Customer() { } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public double getSalary() { return salary; } public void setSalary(double salary) { this.salary = salary; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } @Override public String toString() { return "Customer{" + "id=" + id + ", name='" + name + '\'' + ", gender='" + gender + '\'' + ", age=" + age + ", salary=" + salary + ", phone='" + phone + '\'' + '}'; } }
2.CustomerService代码:
public class CustomerService { private CustomerDao custdao =new CustomerDao(); // 返回所有客户 public List<Customer>getList() { try { return custdao.findAll(); } catch (Exception e) { e.printStackTrace(); } return null; } //返回指定客户 public Customer findCustomer(int id) { try { return custdao.findById(id); } catch (Exception e) { e.printStackTrace(); } return null; } //新增客户 public boolean addCustomer(Customer cust){ try { return custdao.addCustomer(cust); } catch (SQLException throwables) { throwables.printStackTrace(); } return false; } //修改客户 public boolean modifyCustomer(int id ,Customer cust){ try { return custdao.updateCustomer(cust); } catch (SQLException throwables) { throwables.printStackTrace(); } return false; } //删除客户 public Boolean removeCustomer(int id ){ try { return custdao.deleteCustomer(id); } catch (SQLException throwables) { throwables.printStackTrace(); } return false; } }
3. BaseDao和CustomerDao 代码:
public abstract class BaseDao { public int exeupdate(String sql,Object ... params) throws SQLException { //获取连接 Connection connection = JdbcUtil2.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql); int length = params.length; //可变形参可以当成数组使用,遍历赋值 for(int i=0;i<length;i++){ preparedStatement.setObject(i+1,params[i]); } int row = preparedStatement.executeUpdate(); //根据是否开启事务,决定是否关闭连接 if(connection.getAutoCommit()){ JdbcUtil2.freeConnection(); } return row; } //<T>:返回的类型不确认,所以定义为泛型 //:Class<T>clazz, 传入clazz 类型 一是确定T类型,然后为了调用反射给属性赋值 public <T> List<T> executeQuery(Class<T>clazz, String sql , Object ... params) throws Exception { //获取连接 Connection connection = JdbcUtil2.getConnection(); //创建preparedStatement对象 PreparedStatement preparedStatement = connection.prepareStatement(sql); //占位符赋值 for(int i=1;i<=params.length;i++){ preparedStatement.setObject(i,params[i-1]); } //发送数据并返回结果集 ResultSet resultSet = preparedStatement.executeQuery(); //获取列的元数据集 ResultSetMetaData metaData = resultSet.getMetaData(); //获取结果集的列数 int columnCount = metaData.getColumnCount(); List<T> list =new ArrayList<>(); while(resultSet.next()){ //通过反射,获取对象 T t = clazz.newInstance(); for(int i=1;i<columnCount;i++){ //获取列的值 Object object = resultSet.getObject(i); //获取列名或者别名 String columnLabel = metaData.getColumnLabel(i); //通过反射获取 对象的指定属性 Field declaredField = clazz.getDeclaredField(columnLabel); //设置属性可访问 declaredField.setAccessible(true); //给属性赋值 declaredField.set(t,object); } list.add(t); } //关闭资源 resultSet.close(); preparedStatement.close(); //未开启事务 if(connection.getAutoCommit()){ JdbcUtil2.freeConnection(); } return list; } }
public class CustomerDao extends BaseDao{ public List<Customer> findAll() throws Exception { String sql="select * from t_customer"; List<Customer> customers = executeQuery(Customer.class, sql); return customers; } public Customer findById(int id) throws Exception { String sql="select * from t_customer where id=?"; List<Customer> customers = executeQuery(Customer.class, sql, 2); if(customers!=null&&customers.size()>0){ return customers.get(0); } return null; } public boolean addCustomer(Customer cust) throws SQLException { String sql="insert into t_customer (name,gender,age,salary,phone)values(?,? ,?,?,? )"; int rows = exeupdate(sql, cust.getName(), cust.getGender(), cust.getAge(), cust.getSalary(), cust.getPhone()); if(rows==0){ return false; } return true; } public boolean updateCustomer(Customer cust) throws SQLException { String sql="update t_customer set name=?,gender=?, age=?,salary=?,phone=? where id=? "; int rows = exeupdate(sql, cust.getName(), cust.getGender(), cust.getAge(), cust.getSalary(), cust.getPhone(), cust.getId()); if(rows==0){ return false; } return true; } public Boolean deleteCustomer(int id ) throws SQLException { String sql="delete from t_customer where id=? "; int rows = exeupdate(sql, id); if(rows==0){ return false; } return true; } }
4.jdbcUtil工具类代码:
public class JdbcUtil2 { private static DataSource dataSource; private static ThreadLocal<Connection> threadLocal =new ThreadLocal<Connection>(); //静态代码块 实例化datasource static { Properties pro=new Properties(); InputStream input= JdbcUtil2.class.getClassLoader().getResourceAsStream("druid.properties"); try { pro.load(input); } catch (IOException e) { e.printStackTrace(); } try { dataSource = DruidDataSourceFactory.createDataSource(pro); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection() throws SQLException { //从共享线程变量获取连接 Connection connection = threadLocal.get(); if(connection==null){ connection=dataSource.getConnection(); threadLocal.set(connection); } return connection; } public static void freeConnection() throws SQLException { Connection connection = threadLocal.get(); if(connection!=null){ threadLocal.remove(); connection.setAutoCommit(true); connection.close();//回收到线程池 } } }
5.CustomerManage 测试类代码:
public class CustomerManage { public static void main(String[] args) { CustomerService service = new CustomerService(); Customer cust =new Customer(); cust.setName("kate"); cust.setSalary(5000.00); cust.setGender("女"); cust.setPhone("13828384844"); cust.setAge(20); //新增客户 boolean addCustomer = service.addCustomer(cust); System.out.println(addCustomer); //获取所有客户 List<Customer> list = service.getList(); System.out.println(list); //获取指定客户 Customer customer = service.findCustomer(2); System.out.println(customer); //修改客户 Customer customer1 = service.findCustomer(2); customer1.setPhone("8888888"); boolean modifyCustomer = service.modifyCustomer(2, customer1); System.out.println(modifyCustomer); //删除客户 Boolean removeCustomer = service.removeCustomer(3); System.out.println(removeCustomer); } }