mysql的单表crud

1.配置环境

mysql的相关信息，存放在resource下properties文件中

url=jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT&&rewriteBatchedStatements=true
url2=jdbc:mysql://localhost:3306/db3?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT&&rewriteBatchedStatements=true
username=root
password=root

2.JDBC工具类

public class JDBCUtil {

    
    private static Properties properties = new Properties();
    static InputStream in = null;

    // 静态代码块用来读取properties文件和加载数据库驱动
    static{
        try {
            // 通过绝对路径加载mysql.properties
            in = new FileInputStream("D:/JavaFiles/JetBrains/ideaWorkplace/jdbc-base/src/main/resources/mysql.properties");
            properties.load(in);

            // 注册数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
        } catch ( FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 获取数据库db1连接对象
    public static Connection getConnection(){
        String url = properties.getProperty("url"); //url使用数据库db1
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");

        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return connection;
    }

    // 获取数据库db3连接对象
    public static Connection getConnection2(){
        String url = properties.getProperty("url2"); // url2使用数据库db3
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");

        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return connection;
    }

    // 释放资源，先使用的后释放
    public static void close(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){

        try{
            if(resultSet != null){
                resultSet.close();
            }
            if(preparedStatement != null){
                preparedStatement.close();
            }
            if(connection != null){
                connection.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}

3.单表crud操作

public class PersonDaoImpl implements IPersonDao {
    @Override
    public void insert(Person person){
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;

        String sql = "insert into person(name,sex,age) values(?,?,?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,person.getName());
            preparedStatement.setString(2,person.getSex());
            preparedStatement.setInt(3,person.getAge());

            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        JDBCUtil.close(connection,preparedStatement,null);
    }

    @Override
    public void delete(Person person) {
        Connection connection = JDBCUtil.getConnection();

        String sql = "delete from person where id = ?";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,person.getId());
            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        JDBCUtil.close(connection,preparedStatement,null);
    }

    @Override
    public void update(Person person) {
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = null;

        String sql = "update person set name=?,sex=?,age=? where id=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,person.getName());
            preparedStatement.setString(2,person.getSex());
            preparedStatement.setInt(3,person.getAge());
            preparedStatement.setInt(4,person.getId());

            preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        JDBCUtil.close(connection,preparedStatement,null);
    }

    @Override
    public void select() {
        Connection connection = JDBCUtil.getConnection();
        String sql = "select * from person";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        ResultSet resultSet = null;
        try {
            resultSet = preparedStatement.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try{
            while(resultSet.next()){
                Integer id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String sex = resultSet.getString(3);
                Integer age = resultSet.getInt(4);
                Person person = new Person();
                person.setId(id);
                person.setName(name);
                person.setSex(sex);
                person.setAge(age);
                System.out.println(person);
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        JDBCUtil.close(connection,preparedStatement,resultSet);
    }
}

4.事务控制

public class AccountDaoImpl implements IAccountDao {
    @Override
    public void transfer(Account source, Account aim, double money) {
        if(source.getMoney()<money){
            System.out.println("余额不足");
            return;
        }
        source.setMoney(source.getMoney()-money);
        aim.setMoney(aim.getMoney()+money);

        Connection connection2 = JDBCUtil.getConnection2();
        String sql = "update account set money = ? where username = ?";

        PreparedStatement preparedStatement = null;
        try{
            connection2.setAutoCommit(false);

            preparedStatement = connection2.prepareStatement(sql);
            preparedStatement.setDouble(1,source.getMoney());
            preparedStatement.setString(2,source.getUsername());
            preparedStatement.executeUpdate();

            /*int a = 1/0;*/
            preparedStatement.setDouble(1,aim.getMoney());
            preparedStatement.setString(2,aim.getUsername());
            preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            try {
                connection2.rollback();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        try {
            connection2.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        JDBCUtil.close(connection2,preparedStatement,null);


    }
}

5.druid连接池

（1）配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone = GMT&&rewriteBatchedStatements=true
username=root
password=root

（2）Druid工具类

public class DruidUtil {

    static private DataSource dataSource = null;

    static{
        try{
            InputStream in = new FileInputStream("D:/JavaFiles/JetBrains/ideaWorkplace/jdbc-base/src/main/resources/druid.properties");
            Properties properties = new Properties();
            properties.load(in);
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}