jdbc

代码1

import com.mysql.jdbc.Driver;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo7 {
    @Test
    public void test1() throws SQLException {
        //注册驱动
        DriverManager.registerDriver(new Driver());
        //建立连接
        String url="jdbc:mysql://192.168.177.151:4000/test";
        //用户名
        String user="root";
        //密码
        String pwd="123456";
        //获取连接
        Connection connection=DriverManager.getConnection(url,user,pwd);
        //创建命令发送器
        Statement statement=connection.createStatement();
        //准备sql
        String sql="insert into user values(null,'李白','123')";
       //执行sql,获取结果
       int i = statement.executeUpdate(sql);//输出1
       //输出结果
        System.out.println(i);
        //关闭资源
        statement.close();
        connection.close();

    }

    @Test
    public void test2() throws ClassNotFoundException, SQLException {
        //注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取连接
        String url="jdbc:mysql://192.168.177.151:4000/test";
        String user="root";
        String passwd="123456";
        Connection connection=DriverManager.getConnection(url,user,passwd);
        //创建命令发送器
        Statement statement=connection.createStatement();
        //准备sql
        String sql="update user set passwd='456' where id=1";
        //发送sql，获取结果
        int i = statement.executeUpdate(sql);
        System.out.println(i>0?"成功":"失败");
        //关闭资源
        statement.close();
        connection.close();
    }
    
}

代码2

sql查询

    @Test
    public void test2() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://192.168.1.224:3306/test";
        String username="root";
        String passwd="123456";
        Connection connection=DriverManager.getConnection(url,username,passwd);
        Statement statement=connection.createStatement();
        String sql="select * from user";
        ResultSet resultSet=statement.executeQuery(sql);
        while (resultSet.next()){
/*            int idCol=resultSet.getInt(1);
            String name=resultSet.getNString(2);
            Object passw=resultSet.getObject(3);
            System.out.println(idCol+":"+name+":"+passw);*/

/*            int idCol=resultSet.getInt("id");
            String name=resultSet.getString("name");
            String passw=resultSet.getString("passwd");
            System.out.println(idCol+":"+name+":"+passw);*/

            Object idCol=resultSet.getObject("id");
            Object name=resultSet.getObject("name");
            Object passw=resultSet.getObject("passwd");
            System.out.println(idCol+":"+name+":"+passw);
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

代码3

 

    @Test
    public void test3() throws ClassNotFoundException, SQLException {
        Scanner scanner=new Scanner(System.in);
        System.out.println("请输入姓名:");
        String name=scanner.next();

        System.out.println("请输入密码:");
        String passwd=scanner.next();

        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://192.168.1.4:3306/test";
        String username="root";
        String passwd2="123456";
        Connection connection=DriverManager.getConnection(url,username,passwd2);
        Statement statement=connection.createStatement();
        String sql="insert into user values(null,'"+name+"','"+passwd+"')";
        int i=statement.executeUpdate(sql);
        System.out.println(i>0?"成功":"失败");
        statement.close();
        connection.close();
    }

代码4

    @Test
    public void test4() throws ClassNotFoundException, SQLException {
        Scanner scanner=new Scanner(System.in);
        System.out.println("input username:");
        String sname=scanner.next();
        System.out.println("input password:");
        String pwd=scanner.next();

        User user=judgeUser(sname,pwd);
        if (user!=null){
            System.out.println(user);
        }else {
            System.out.println("查无此人");
        }
    }

    private User judgeUser(String sname, String pwd) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test","root","123456");
        Statement statement=connection.createStatement();
        String sql="select * from user where name='"+sname+"' and passwd = '"+pwd+"'";
        ResultSet resultSet=statement.executeQuery(sql);
        while (resultSet.next()){
            int id=resultSet.getInt(1);
            String name=resultSet.getString(2);
            String pwd1=resultSet.getString(3);
            User user=new User(name,pwd1);
            return user;
        }
        resultSet.close();
        statement.close();
        connection.close();
        return null;
    }

代码5

变量方式插入数据

    @Test
    public void test4() throws ClassNotFoundException, SQLException {
        Scanner scanner=new Scanner(System.in);
        System.out.println("input username:");
        String sname=scanner.next();
        System.out.println("input password:");
        String pwd=scanner.next();
        judgeUser(sname,pwd);

    }


    private void judgeUser(String sname, String pwd) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test","root","123456");
        Statement statement=connection.createStatement();
        String sql="insert into user values (null,?,?)";
        PreparedStatement pst =connection.prepareStatement(sql);
        pst.setObject(1,sname);
        pst.setObject(2,pwd);

        int i=pst.executeUpdate();
        System.out.println(i>0?"成功":"失败");

        pst.close();
        statement.close();
        connection.close();
    }

代码6

 

变量方式查询

    @Test
    public void test4() throws ClassNotFoundException, SQLException {
        Scanner scanner=new Scanner(System.in);
        System.out.println("input username:");
        String sname=scanner.next();
        System.out.println("input password:");
        String pwd=scanner.next();
        User user = judgeUser(sname,pwd);
        if(user!=null){
            System.out.println(user);
        }else {
            System.out.println("查无此人");
        }
    }


    private User judgeUser(String sname, String pwd) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test","root","123456");
        Statement statement=connection.createStatement();
        String sql="select * from user where name=? and passwd=?";
        PreparedStatement pst =connection.prepareStatement(sql);
        pst.setObject(1,sname);
        pst.setObject(2,pwd);
        ResultSet resultSet=pst.executeQuery();

        while (resultSet.next()){
            int id=resultSet.getInt(1);
            String name=resultSet.getString(2);
            String password=resultSet.getString(3);
            User user=new User(name,password);
            return user;
        }

        pst.close();
        statement.close();
        connection.close();
        return null;
    }

代码7

插入图片

    @Test
    public void test5() throws ClassNotFoundException, SQLException, IOException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test","root","123456");
        String sql="insert into photo values(null,?)";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        InputStream ins=new FileInputStream(new File("C:\\Users\\test\\Desktop\\项目.png"));
        preparedStatement.setBlob(1,ins);
        int i=preparedStatement.executeUpdate();
        System.out.println(i>0?"成功":"失败");
        ins.close();
        preparedStatement.close();
        connection.close();

    }

代码8

 获取自增id

    @Test
    public void test6() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test","root","123456");
        String sql="insert into user values(null,?,?)";
        PreparedStatement preparedStatement=connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setObject(1,"宝玉");
        preparedStatement.setObject(2,"123");
        int i=preparedStatement.executeUpdate();
        System.out.println(i>0?"成功":"失败");
        ResultSet generateKeys=preparedStatement.getGeneratedKeys();
        if (generateKeys.next()){
            Object object=generateKeys.getObject(1);
            System.out.println(object);
        }

        preparedStatement.close();
        connection.close();
    }

代码7

批量插入数据

    @Test
    public void test7() throws ClassNotFoundException, SQLException {
        long startTtime=System.currentTimeMillis();
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test?rewriteBatchedStatement","root","123456");
        String sql="insert into user values(null,?,?)";
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        for (int i = 0; i <1000 ; i++) {
            preparedStatement.setObject(1,"小梦"+i);
            preparedStatement.setObject(2,"123"+i);
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();

        long endTime=System.currentTimeMillis();
        System.out.println("耗时："+(endTime-startTtime));
        preparedStatement.close();
        connection.close();
    }

代码8

 事务

    @Test
    public void test8(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection=DriverManager.getConnection("jdbc:mysql://192.168.1.14:3306/test?rewriteBatchedStatement","root","123456");
            String sql1="update account set balance=balance-500 where name='李白'";
            String sql2="update account set balance=balance-500 where name='杜甫'";

            PreparedStatement preparedStatement1=connection.prepareStatement(sql1);
            PreparedStatement preparedStatement2=connection.prepareStatement(sql2);

            preparedStatement1.executeUpdate();
            preparedStatement2.executeUpdate();

            System.out.println("转账成功");

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

代码9

 开始事务，关闭自动提交

    @Test
    public void test8() {
        Connection connection=null;
        PreparedStatement preparedStatement1=null;
        PreparedStatement preparedStatement2=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection=DriverManager.getConnection("jdbc:mysql://192.168.1.4:3306/test?rewriteBatchedStatement","root","123456");
            connection.setAutoCommit(false);
            String sql1="update account set balance=balance-500 where name='李白'";
            String sql2="update account set balance=balance-500 where name='杜甫'";
            preparedStatement1=connection.prepareStatement(sql1);
            preparedStatement2=connection.prepareStatement(sql2);

            preparedStatement1.executeUpdate();
            preparedStatement2.executeUpdate();

            connection.commit();
            System.out.println("转账成功");


        } catch (ClassNotFoundException | SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            System.out.println(e.getMessage());
        }finally {
            if (preparedStatement1!=null){
                try {
                    preparedStatement1.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (preparedStatement2!=null){
                try {
                    preparedStatement2.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

代码10

工具类

package day19.utils;
import java.sql.*;

public class JDBCUtils {
    //获取连接
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://192.168.1.4:3306/test";
        String user="root";
        String pwd="123456";
        Connection connection= DriverManager.getConnection(url,user,pwd);
        return connection;
    }
    //关闭资源
    public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (connection!=null){
            connection.close();
        }
        if (statement!=null){
            statement.close();
        }
        if (resultSet!=null){
            resultSet.close();
        }
    }
}

之前的事务代码可以改成：
    @Test
    public void test8() {
        Connection connection=null;
        PreparedStatement preparedStatement1=null;
        PreparedStatement preparedStatement2=null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection=JDBCUtils.getConnection();
            connection.setAutoCommit(false);
            String sql1="update account set balance=balance-500 where name='李白'";
            String sql2="update account set balance=balance+500 where name='杜甫'";
            preparedStatement1=connection.prepareStatement(sql1);
            preparedStatement2=connection.prepareStatement(sql2);

            preparedStatement1.executeUpdate();
            preparedStatement2.executeUpdate();

            connection.commit();
            System.out.println("转账成功");


        } catch (ClassNotFoundException | SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            System.out.println(e.getMessage());
        }finally {
            try {
                JDBCUtils.closeResources(connection,preparedStatement1,null);
                JDBCUtils.closeResources(null,preparedStatement2,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

代码11.

properties使用

import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Properties;


public class PropertiesTest {
    @Test
    public void test01(){
        //获取系统的配置信息
        Properties properties=System.getProperties();
        //输出所有的配置信息
        properties.list(System.out);

        //输出具体配置信息
        String codeValue=properties.getProperty("file.encoding");
        System.out.println(codeValue);//UTF-8
    }

    @Test
    public void test02() throws IOException {
        Properties properties=new Properties();
        properties.load(new FileInputStream(new File("C:\\Users\\test\\Downloads\\abc.properties")));
        System.out.println(properties);//{user=root, password=123456}

    }
}

代码12

通过properties优化工具类

jdbc.properties

user=root
password=123456
url=jdbc:mysql://192.168.1.4:3306/test
driver=com.mysql.jdbc.Driver

-------------------------
JDBCUtils.java 

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
       Connection connection= getConnection();
        System.out.println(connection);
    }
    //获取连接
    static String user;
    static String password;
    static String url;
    static {
        Properties properties=new Properties();
        try {
            //properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties"));
            properties.load(new FileInputStream(new File("C:\\Users\\test\\IdeaProjects\\untitled\\src\\day19\\jdbc.properties")));
            user=properties.getProperty("user");
            url=properties.getProperty("url");
            password=properties.getProperty("password");
            String driver=properties.getProperty("driver");

            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        }catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws ClassNotFoundException, SQLException, IOException {
        Connection connection=DriverManager.getConnection(url,user,password);
        return connection;
    }
    //关闭资源
    public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (connection!=null){
            connection.close();
        }
        if (statement!=null){
            statement.close();
        }
        if (resultSet!=null){
            resultSet.close();
        }
    }
}

--------------------------

    @Test
    public void test8() {
        Connection connection=null;
        PreparedStatement preparedStatement1=null;
        PreparedStatement preparedStatement2=null;
        try {
            connection=JDBCUtils.getConnection();
            connection.setAutoCommit(false);
            String sql1="update account set balance=balance-500 where name='李白'";
            String sql2="update account set balance=balance+500 where name='杜甫'";
            preparedStatement1=connection.prepareStatement(sql1);
            preparedStatement2=connection.prepareStatement(sql2);

            preparedStatement1.executeUpdate();
            preparedStatement2.executeUpdate();

            connection.commit();
            System.out.println("转账成功");


        } catch (ClassNotFoundException | SQLException | IOException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            System.out.println(e.getMessage());
        }finally {
            try {
                JDBCUtils.closeResources(connection,preparedStatement1,null);
                JDBCUtils.closeResources(null,preparedStatement2,null);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

代码13

druid示例

https://repo1.maven.org/maven2/com/alibaba/druid/1.0.10/druid-1.0.10.jar

把druid-1.0.10.jar包放到lib目录下


    @Test
    public void test9() throws SQLException {
        //创建对象
        DruidDataSource druidDataSource=new DruidDataSource();
        //基本配置
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("123456");
        druidDataSource.setUrl("jdbc:mysql://192.168.1.4:3306/test");
        //连接池配置
        druidDataSource.setInitialSize(5);//连接池初始化5条连接
        druidDataSource.setMaxActive(10);//最多可以有几个连接
        druidDataSource.setMaxWait(2000);//最多等待时间
        //获取连接
        for (int i = 0; i < 20; i++) {
            DruidPooledConnection connection=druidDataSource.getConnection();
            System.out.println(connection);
            connection.close();
        }
    }

//输出

三月 18, 2022 11:30:45 上午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3
com.mysql.jdbc.JDBC4Connection@4590c9c3

代码14

 druid连接池

    @Test
    public void test9() throws SQLException {
        //创建对象
        DruidDataSource druidDataSource=new DruidDataSource();
        //基本配置
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("123456");
        druidDataSource.setUrl("jdbc:mysql://192.168.1.4:3306/test");
        //连接池配置
        druidDataSource.setInitialSize(5);//连接池初始化5条连接
        druidDataSource.setMaxActive(10);//最多可以有几个连接
        druidDataSource.setMaxWait(2000);//最多等待时间
        //获取连接
        for (int i = 0; i < 20; i++) {
            DruidPooledConnection connection=druidDataSource.getConnection();
            System.out.println(connection);
            connection.close();
        }
    }

代码15

完整druid工具代码

druid.properties
----------------------------------------------
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.1.4:3306/test?rewriteBatchedStatements=true
username=root
password=123456
initialSize=5
maxActive=10
maxWait=1000

JDBCUtils2.class
-------------------------------------------

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils2 {
    public static void main(String[] args) throws SQLException {
       Connection connection=getConnection();
        System.out.println(connection);
    }

    static DataSource dataSource;
    static {
        Properties properties=new Properties();
        try {
          //  properties.load(JDBCUtils2.class.getClassLoader().getResourceAsStream("druid.properties"));
            properties.load(new FileInputStream(new File("C:\\Users\\test\\IdeaProjects\\untitled\\src\\day19\\druid.properties")));
            dataSource=DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        Connection connection=dataSource.getConnection();
        return connection;
    }
    //关闭资源
    public static void closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (resultSet!=null){
            resultSet.close();
        }
        if (statement!=null){
            resultSet.close();
        }
        if (connection!=null){
            connection.close();
        }
    }
}


BaseDao.class
-----------------------------------------

import day19.utils.JDBCUtils2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BaseDao {
    public int commonUpdate(String sql,Object...objs) throws SQLException {
        //获取连接
        Connection connection= JDBCUtils2.getConnection();
        //准备sql，准备命令发送器
        PreparedStatement preparedStatement=connection.prepareStatement(sql);

        if (objs!=null && objs.length>0){
            for (int i = 0; i <objs.length ; i++) {
                preparedStatement.setObject(i+1,objs[i]);
            }
        }

        //获取执行命令结果
        int i=preparedStatement.executeUpdate();
        return i;
    }
}


测试代码
------------------------------------
    @Test
    public void test10() throws SQLException {
        BaseDao baseDao=new BaseDao();
        String sql="insert into user values(null,?,?)";
        Object[] o={"李小白","123456"};
        int i=baseDao.commonUpdate(sql,o);
        System.out.println(i);
    }

代码16

整合后的查询

package day19;

public class Account {
    private int id;
    private String sname;
    private int balance;

    public Account() {
    }

    public Account(int id, String sname, int balance) {
        this.id = id;
        this.sname = sname;
        this.balance = balance;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public int getBalance() {
        return balance;
    }

    public void setBalance(int balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", sname='" + sname + '\'' +
                ", balance=" + balance +
                '}';
    }
}

    @Test
    public void test1() throws SQLException {
        //注册驱动，获取连接
        Connection connection= JDBCUtils2.getConnection();
        //准备sql
        String sql="select * from account";
        //创建命令发送器
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        ResultSet resultSet=preparedStatement.executeQuery();
        //处理结果
        while (resultSet.next()){
            Object o1=resultSet.getObject(1);
            Object o2=resultSet.getObject(2);
            Object o3=resultSet.getObject(3);
            System.out.println(o1+"-"+o2+"-"+o3);
        }
        JDBCUtils2.closeResources(connection,preparedStatement,resultSet);
    }

代码17

 获取表头和字段

    @Test
    public void test1() throws SQLException {
        //注册驱动，获取连接
        Connection connection= JDBCUtils2.getConnection();
        //准备sql
        String sql="select * from account";
        //创建命令发送器
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        //获取表头的数据
        ResultSetMetaData metaData=preparedStatement.getMetaData();
        //获取字段数
        int count=metaData.getColumnCount();
        System.out.println("字段数:"+count);
        for (int i = 0; i <count ; i++) {
            System.out.println(metaData.getColumnName(i+1));//输出字段名
        }
        //执行命令获取结果
        ResultSet resultSet=preparedStatement.executeQuery();
        //处理结果
        while (resultSet.next()){
            Object o1=resultSet.getObject(1);
            Object o2=resultSet.getObject(2);
            Object o3=resultSet.getObject(3);
            System.out.println(o1+"-"+o2+"-"+o3);
        }
        JDBCUtils2.closeResources(connection,preparedStatement,resultSet);
    }

代码18

获取字段别名，表名，字段类型

    @Test
    public void test1() throws SQLException {
        //注册驱动，获取连接
        Connection connection= JDBCUtils2.getConnection();
        //准备sql
        String sql="select id,name sname,balance balances from account";
        //创建命令发送器
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        //获取表头的数据
        ResultSetMetaData metaData=preparedStatement.getMetaData();
        //获取字段数
        int count=metaData.getColumnCount();
        System.out.println("字段数:"+count);
        for (int i = 0; i <count ; i++) {
            System.out.println(metaData.getColumnName(i+1));//输出字段名
            System.out.println(metaData.getColumnLabel(i+1));//输出字段别名
            System.out.println(metaData.getColumnClassName(i+1));//输出字段类型
            System.out.println(metaData.getTableName(i+1));//输出表名
            System.out.println("------------------------");
        }
        //执行命令获取结果
        ResultSet resultSet=preparedStatement.executeQuery();
        //处理结果
        while (resultSet.next()){
            Object o1=resultSet.getObject(1);
            Object o2=resultSet.getObject(2);
            Object o3=resultSet.getObject(3);
            System.out.println(o1+"-"+o2+"-"+o3);
        }
        JDBCUtils2.closeResources(connection,preparedStatement,resultSet);
    }

代码19

通用增删改查代码

import day19.utils.JDBCUtils2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BaseSelectDao {
    public int update(String sql,Object...args) throws SQLException {
        //获取连接
        Connection connection= JDBCUtils2.getConnection();
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        if (args!=null && args.length>0){
            for (int i = 0; i <args.length ; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
        }

        int i = preparedStatement.executeUpdate();
        return i;
    }
}

    @Test
    public void test2() throws SQLException {
        BaseSelectDao baseSelectDao=new BaseSelectDao();
        String sql="insert into account values (null,?,?)";
        Object[] objects={"杜小甫",1000};
        int i =baseSelectDao.update(sql,objects);
        System.out.println(i);
    }

 

代码20

通用查询

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseAll {

    public int update(String sql,Object...args){
       return 1;
    }

    /*
    * 获取查询的数据封装为集合
    * @param clazz对应类型的class对象
    * @param sql要执行的sql
    * @param args要传递的参数
    * @param <T> 具体的类型
    * @return
    * */
    public <T> List<T> getAll(Class<T> clazz, String sql, Object...args) throws SQLException, IllegalAccessException, InstantiationException, NoSuchFieldException {
        //创建存储所有数据的集合
        List<T> list=new ArrayList<>();
        //获取连接
        Connection connection=JDBCUtils2.getConnection();
        //准备命令发送器
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        //获取元数据
        ResultSetMetaData metaData=preparedStatement.getMetaData();
        //获取列
        int count = metaData.getColumnCount();

        //设置值
        if (args!=null && args.length>0){
            for (int i = 0; i <args.length ; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
        }
        //执行命令，获取结果集
        ResultSet resultSet=preparedStatement.executeQuery();
        while (resultSet.next()){
            //使用反射创建对象
            T instance=clazz.newInstance();
            //获取字段名再获取对应的值
            for (int i = 0; i <count ; i++) {
                //获取字段名
                String columnlabel=metaData.getColumnLabel(i+1);
                //获取对应的值
                Object object=resultSet.getObject(columnlabel);
                //获取字段对应的属性
                Field field=clazz.getDeclaredField(columnlabel);
                field.setAccessible(true);
                //将值变为对象的属性值
                field.set(instance,object);
            }
            //将对象加到集合内
            list.add(instance);
        }
        return list;
    }
}

import com.mysql.jdbc.Driver;
import org.junit.Test;
import com.atguigu.day19.Account;
import java.sql.*;
import java.util.List;
import java.util.Properties;
import java.util.logging.Logger;

public class Demo1 {
    @Test
    public void test1() throws SQLException, IllegalAccessException, NoSuchFieldException, InstantiationException {
        BaseAll baseAll=new BaseAll();
        String sql="select id,name sname,balance from account";
        List<Account> all= baseAll.getAll(Account.class,sql,null);
        all.forEach(System.out::println);
    }
}

代码21

    @Test
    public void test2() throws SQLException, NoSuchFieldException, InstantiationException, IllegalAccessException {
        BaseAll baseAll=new BaseAll();
        String sql="select * from user";
        List<User> all=baseAll.getAll(User.class,sql,null);
        all.forEach(System.out::println);
    }
    @Test
    public void test3() throws SQLException, NoSuchFieldException, InstantiationException, IllegalAccessException {
        BaseAll baseAll=new BaseAll();
        String sql="select * from user where id>? and passwd=?";
        Object[] arr={3,"123"};
        List<User> all=baseAll.getAll(User.class,sql,arr);
        all.forEach(System.out::println);

    }

代码22

获取单个值

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BaseAll {

    public int update(String sql, Object... args) {
        return 1;
    }

    /*
     * 获取查询的数据封装为集合
     * @param clazz对应类型的class对象
     * @param sql要执行的sql
     * @param args要传递的参数
     * @param <T> 具体的类型
     * @return
     * */
    public <T> List<T> getAll(Class<T> clazz, String sql, Object... args) throws SQLException, IllegalAccessException, InstantiationException, NoSuchFieldException {
        //创建存储所有数据的集合
        List<T> list = new ArrayList<>();
        //获取连接
        Connection connection = JDBCUtils2.getConnection();
        //准备命令发送器
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //获取元数据
        ResultSetMetaData metaData = preparedStatement.getMetaData();
        //获取列
        int count = metaData.getColumnCount();

        //设置值
        if (args != null && args.length > 0) {
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
        }
        //执行命令，获取结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            //使用反射创建对象
            T instance = clazz.newInstance();
            //获取字段名再获取对应的值
            for (int i = 0; i < count; i++) {
                //获取字段名
                String columnlabel = metaData.getColumnLabel(i + 1);
                //获取对应的值
                Object object = resultSet.getObject(columnlabel);
                //获取字段对应的属性
                Field field = clazz.getDeclaredField(columnlabel);
                field.setAccessible(true);
                //将值变为对象的属性值
                field.set(instance, object);
            }
            //将对象加到集合内
            list.add(instance);
        }
        return list;
    }

    //获取单个值
    public <T> T getOne(Class<T> clazz, String sql, Object... args) throws SQLException, NoSuchFieldException, InstantiationException, IllegalAccessException {
        List<T> all = getAll(clazz, sql, args);
        T t = all.get(0);
        return t;
    }
}

------------------------------------------
    @Test
    public void test4() throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException {
        BaseAll baseOne=new BaseAll();
        String sql="select * from user where id=?";
        User one=baseOne.getOne(User.class,sql,5);
        System.out.println(one);
    }

代码23

    //通用的没有实体类对应的编码

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BaseAll {

    /**
     * 通用的没有实体类对应的编码
     * @param sql
     * @param args
     * @return
     */
     public List<Map<String,Object>> mapToList(String sql,Object...args) throws SQLException {
        //创建集合
         List<Map<String,Object>> list=new ArrayList<>();
         //获取连接
         Connection connection=JDBCUtils2.getConnection();
         //创建命令发送器
         PreparedStatement preparedStatement=connection.prepareStatement(sql);
         //设置值
         if (args!=null && args.length>0){
             for (int i = 0; i < args.length; i++) {
                 preparedStatement.setObject(i+1,args[i]);
             }
         }
         //获取元数据
         ResultSetMetaData metaData = preparedStatement.getMetaData();
         //获取一共多少字段
         int columnCount = metaData.getColumnCount();
         //执行命令获取结果
         ResultSet resultSet = preparedStatement.executeQuery();
         //遍历结果
         while (resultSet.next()){
             //创建map，存储值
             Map<String,Object> map=new HashMap<>();
             for (int i = 0; i <columnCount ; i++) {
                //获取字段名
                 String columnLabel = metaData.getColumnLabel(i+1);
                 //获取字段值
                 Object object = resultSet.getObject(columnLabel);

                 map.put(columnLabel,object);
             }
             //将map添加到集合内
             list.add(map);
         }
         //关闭资源
         JDBCUtils2.closeResources(connection,preparedStatement,resultSet);

        return list;
     }
}

    @Test
    public void test5() throws SQLException {
        BaseAll baseAll=new BaseAll();
        String sql="select uid,avg(balance) avg_balance,max(balance) max_balance from account2 where uid>? group by uid";
        List<Map<String,Object>> list=baseAll.mapToList(sql,180438);
        System.out.println(list);
        list.forEach(System.out::println);
    }

代码24

未使用ThreadLocal完成事务控制

    @Test
    public void test3(){
        String sql1="update account set balance=balance-500 where id=1";
        String sql2="update account set balance=balance+500 where id=2";
        BaseDao baseDao=new BaseDao();
        Connection connection=null;

        try {
            connection=JDBCUtils2.getConnection();
            System.out.println("连接："+connection+",线程："+ Thread.currentThread().getName());
            connection.setAutoCommit(false);
            baseDao.commonUpdate(sql1,null);
            baseDao.commonUpdate(sql2,null);
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }         
    }

代码25

使用ThreadLocal完成事务控制

 

JDBCUtils2
--------------------------------------
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils2 {
    public static void main(String[] args) throws SQLException {
       Connection connection=getConnection();
        System.out.println(connection);
    }

    static DataSource dataSource;
    static ThreadLocal<Connection> threadLocal=new ThreadLocal<>();
    static {
        Properties properties=new Properties();
        try {
          //  properties.load(JDBCUtils2.class.getClassLoader().getResourceAsStream("druid.properties"));
            properties.load(new FileInputStream(new File("C:\\Users\\test\\IdeaProjects\\untitled\\src\\day19\\druid.properties")));
            dataSource=DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } 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 closeResources(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
        if (resultSet!=null){
            resultSet.close();
            threadLocal.remove();
        }
        if (statement!=null){
            resultSet.close();
        }
        if (connection!=null){
            connection.close();
        }
    }
}

-----------------------------------------------
    /**
     * 使用ThreadLocal完成事务控制
     */
    @Test
    public void test3(){
        String sql1="update account set balance=balance-500 where id=1";
        String sql2="update account set balance=balance+500 where id=2";
        BaseDao baseDao=new BaseDao();
        Connection connection=null;

        try {
            connection=JDBCUtils2.getConnection();
            System.out.println("连接："+connection+",线程："+ Thread.currentThread().getName());
            connection.setAutoCommit(false);
            baseDao.commonUpdate(sql1,null);
            baseDao.commonUpdate(sql2,null);
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

代码26

 通用获取一个值的方法

BaseDao 
--------------------

import day19.utils.JDBCUtils2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BaseDao {
    public int commonUpdate(String sql,Object...objs) throws SQLException {
        //获取连接
        Connection connection= JDBCUtils2.getConnection();
        //准备sql，准备命令发送器
        PreparedStatement preparedStatement=connection.prepareStatement(sql);

        if (objs!=null && objs.length>0){
            for (int i = 0; i <objs.length ; i++) {
                preparedStatement.setObject(i+1,objs[i]);
            }
        }

        //获取执行命令结果
        int i=preparedStatement.executeUpdate();
        return i;
    }

    /**
     * 通用获取一个值的方法
     * @param sql
     * @param args
     * @return
     */
    public Object getSingleValue(String sql,Object...args) throws SQLException {
        //获取连接
        Connection connection=JDBCUtils2.getConnection();
        //创建命令发送器
        PreparedStatement preparedStatement=connection.prepareStatement(sql);
        //设置值
        if (args!=null && args.length>0){
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
        }

        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            Object object=resultSet.getObject(1);
            return object;
        }
        return null;
    }
}

-----------------------
    @Test
    public void test4() throws SQLException {
        BaseDao baseDao = new BaseDao();
        String sql="select count(*) from account";
        Object singleValue=baseDao.getSingleValue(sql,null);
        System.out.println(singleValue);
    }

代码27

common-dbutils

package day19;

import day19.utils.JDBCUtils2;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.management.Query;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class BaseDao2 {
    QueryRunner queryRunner=new QueryRunner();

    /**
     *通用的增删改
     * @param sql
     * @param args
     * @return
     * @throws SQLException
     */
    public int update(String sql,Object...args) throws SQLException {
        Connection connection= JDBCUtils2.getConnection();
        return queryRunner.update(connection,sql,args);
    }

    public <T> List<T> getAll(Class<T> clazz,String sql,Object...args) throws SQLException {
        Connection connection=JDBCUtils2.getConnection();
        return queryRunner.query(connection,sql,new BeanListHandler<>(clazz),args);
    }

    public <T> T getOne(Class<T> clazz,String sql,Object...args) throws SQLException {
        return queryRunner.query(JDBCUtils2.getConnection(),sql,new BeanHandler<>(clazz),args);
    }

    public List<Map<String,Object>> mapTolist(String sql,Object...args) throws SQLException {
        return queryRunner.query(JDBCUtils2.getConnection(),sql,new MapListHandler(),args);
    }

    public Object getSingle(String sql,Object...args) throws SQLException {
        return queryRunner.query(JDBCUtils2.getConnection(),sql,new ScalarHandler<>(),args);
    }
}

    //update
    @Test
    public void test5() throws SQLException {
        BaseDao2 baseDao2=new BaseDao2();
        String sql="update user set name='杜小甫' where id=?";
        Object[] arr={1};
        int i=baseDao2.update(sql,arr);
        System.out.println(i>0?"成功":"失败");
    }

    //getall
    @Test
    public void test6() throws SQLException {
        BaseDao2 baseDao2 = new BaseDao2();
        String sql="select id,name sname,balance from account where id between ? and ?";
        Object[] arr={1,5};
        List<Account> all=baseDao2.getAll(Account.class,sql,arr);
        all.forEach(System.out::println);
    }

    //getone
    @Test
    public void test7() throws SQLException {
        BaseDao2 baseDao2 = new BaseDao2();
        String sql="select id,name sname, passwd pwd from user where id=?";
        Object[] arr={5};
        User one = baseDao2.getOne(User.class, sql, arr);
        System.out.println(one);
    }

    //mapTolist
    @Test
    public void test8() throws SQLException {
        BaseDao2 baseDao2 = new BaseDao2();
        String sql="select * from account where id between ? and ?";
        Object[] arr={1,5};
        List<Map<String, Object>> maps = baseDao2.mapTolist(sql,arr);
        for (Map<String, Object> map : maps) {
            System.out.println(map);
        }
    }
    //getSingle
    @Test
    public void test9() throws SQLException {
        BaseDao2 baseDao2 = new BaseDao2();
        String sql="select name from user where id=?";
        Object[] arr={6};
        Object single = baseDao2.getSingle(sql, arr);
        System.out.println(single);
    }