5.16java操作数据库

 BaseDAO封装通用方法

 BaseDAO代码

package com.qmandes.util;




import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BaseDAO {

    private static String driver = "com.mysql.cj.jdbc.Driver";
    private static  String url = "jdbc:mysql://localhost:3306/test516?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    private static  String user = "root";
    private static String password = "root";

    public static Connection getConnection(){
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;

    }
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
        try{
            if (resultSet != null){
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement != null){
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try{
                    if (connection != null){
                        connection.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public static void setParams(PreparedStatement preparedStatement,Object[] params){
        if(params == null) return;
        for(int i = 0; i < params.length; i ++){
            try {
                preparedStatement.setObject(i+1,params[i]);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static int executeUpdate(String sql,Object[] params){
        int res = -1;
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            setParams(preparedStatement,params);
            res = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(connection,preparedStatement,null);
        }
        return res;
    }
    public static List<Map> exectueQuery(String sql,Object[] params){
        List<Map> rows = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            setParams(preparedStatement,params);
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int colscount = resultSetMetaData.getColumnCount();
            while (resultSet.next()){
                Map<String,Object> map = new HashMap<>();
                for(int i = 1; i <= colscount; i ++){
                    map.put(resultSetMetaData.getColumnName(i),resultSet.getObject(i));
                }
                rows.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeAll(connection,preparedStatement,resultSet);
        }

        return rows;
    }
}

测试

package com.qmandes.sql.demo516.lessons.test2;

import com.qmandes.util.BaseDAO;
import org.junit.Test;

import java.sql.*;
import java.util.List;
import java.util.Map;

public class TestBaseDAO {
    @Test
    public void insertSql(){

        String name = "红楼梦";
        String author = "曹雪芹";
        String pubdate = "2000-11-11";
        double price = 20.55;
        Object[] params = {name,author,pubdate,price};
        String sql = "INSERT INTO books VALUES (NULL,?,?,?,?)";
        int count = BaseDAO.executeUpdate(sql,params);
        System.out.println("更新了"+count+"条数据");


    }
    @Test
    public void delSql(){

        String keyword = "红楼梦";
        String sql = "DELETE FROM books WHERE bookname = ?";
        Object[] params = {keyword};
        int count = BaseDAO.executeUpdate(sql,params);
        System.out.println("更新了"+count+"条数据");
    }
    @Test
    public void updateSql(){

        String oldkeyword = "红楼梦";
        String newkeyword = "西游记";
        String sql = "UPDATE books SET bookname = ? WHERE bookname = ?";
        Object[] params = {newkeyword,oldkeyword};
        int count = BaseDAO.executeUpdate(sql,params);
        System.out.println("更新了"+count+"条数据");
    }
    @Test
    public void querySql(){

        String keyword = "西游记";
        String sql = "SELECT * FROM books WHERE bookname = ?";
        Object[] params = {keyword};
        List<Map> list = BaseDAO.exectueQuery(sql,params);
        for (Map map:list) {
            System.out.println(map);
        }
    }
    @Test
    public void affairsSql(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql1 = "UPDATE bank SET money = money - 3000 WHERE account = '张三'";
        String sql2 = "UPDATE bank SET money = money + 3000 WHERE account = '李四'";

        try {
            connection = BaseDAO.getConnection();
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql1);
            int count = preparedStatement.executeUpdate();
            System.out.println("插入了" + count + "条数据");

            preparedStatement = connection.prepareStatement(sql2);
            count = preparedStatement.executeUpdate();
            System.out.println("插入了" + count + "条数据");
            connection.commit();
            System.out.println("转账成功");

        } catch (Exception e) {
            System.out.println("出现错误");
            try {
                connection.rollback();
                System.out.println("rollback");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            BaseDAO.closeAll(connection,preparedStatement,null);
        }
    }
}