【JAVA JDBC】

一、数据的持久化

1、 数据的持久化
持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。

持久化的主要应用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中

二、链接数据库的三要素：

1、Driver接口实现类

- Oracle的驱动：oracle.jdbc.driver.OracleDriver
- mySql 的驱动：com.mysql.jdbc.Driver

加载驱动：加载 DBUtils 驱动需调用 Class 类的静态方法 forName()，向其传递要加载的 DBUtils 驱动的类名

Class.forName(“com.mysql.jdbc.Driver”);

注册驱动：DriverManager 类是驱动程序管理器类，负责管理驱动程序

DriverManager.registerDriver(com.mysql.jdbc.Driver)

通常不用显式调用 DriverManager 类的 registerDriver() 方法来注册驱动程序类的实例，因为 Driver 接口的驱动程序类都包含了静态代码块，在这个静态代码块中，会调用 DriverManager.registerDriver() 方法来注册自身的一个实例。
2、URL

jdbc（协议）:mysql (驱动) : / /127.0.0.1（ip）:3306（端口）/partice(数据库)?useUnicode=true&characterEncoding=utf(字符集)8&useSSL=false（加密协议）&serverTimezone=Asia/Shanghai（地址）

• jdbc:子协议:子名称
• 协议：DBUtils URL中的协议总是jdbc
• 子协议：子协议用于标识一个数据库驱动程序
• 子名称：一种标识数据库的方法。子名称可以依不同的子协议而变化，用子名称的目的是为了定位数据库提供足够的信息。包含主机名(对应服务端的ip地址)，端口号，数据库名

3、用户名和密码
user,password可以用“属性名=属性值”方式告诉数据库
可以调用 DriverManager 类的 getConnection() 方法建立到数据库的链接
测试链接

   @Test
    public void testConnection1() throws Exception {
        //1.数据库连接的4个基本要素：
        String url = "jdbc:mysql://127.0.0.1:3306/partice?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "123456";
        //8.0之后名字改了  com.mysql.cj.jdbc.Driver
        String driverName = "com.mysql.cj.jdbc.Driver";

        //2.实例化Driver
        Class clazz = Class.forName(driverName);
        Driver driver = (Driver) clazz.newInstance();
        //3.注册驱动
        DriverManager.registerDriver(driver);
        //4.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);

    }

    @Test
    public void testConnection2() throws Exception {
        //1.数据库连接的4个基本要素：
        String url = "jdbc:mysql://127.0.0.1:3306/partice?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "123456";
        //8.0之后名字改了  com.mysql.cj.jdbc.Driver
        String driverName = "com.mysql.cj.jdbc.Driver";

        //2.实例化Driver
        Class.forName(driverName);

        //3.注册驱动

        //4.获取连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);

    }

    @Test
    public void testConnection3() throws Exception {
        //1.数据库连接的4个基本要素：
        String url = "jdbc:mysql://127.0.0.1:3306/partice?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "123456";
        //8.0之后名字改了  com.mysql.cj.jdbc.Driver
        //META-INF services java.sql.Driver帮我们做了拿包进行注册
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);

    }

    @Test
    public void testConnection4() throws Exception {
        Properties properties = new Properties();
        properties.load(JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
        String url = properties.getProperty("mysql.url");
        String user = properties.getProperty("mysql.username");
        String password = properties.getProperty("mysql.password");
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);

    }

jdbc.properties

mysql.username=root
mysql.password=123456
mysql.url=jdbc:mysql://127.0.0.1:3306/partice?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
mysql.driverName=com.mysql.cj.jdbc.Driver

三、使用PreparedStatement（防止sql注入，预处理）

statement

    @Test
    public void testStatement() throws Exception {
        Connection conn = JdbcUtil.getConnection();
        Statement statement = conn.createStatement();
        String sql = "insert into user values (5,'cc','12')";
        statement.execute(sql);
    }

    @Test
    public void testStatement2() throws Exception {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            conn = JdbcUtil.getConnection();
            statement = conn.createStatement();
            String sql = "select  * from user";
            resultSet = statement.executeQuery(sql);
            List<User> users = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                users.add(new User(id, name, age));
            }
            System.out.println(users);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, statement, resultSet);
        }
    }

preStatement

    @Test
    public void preStatement() throws Exception {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "select * from login where id>?";
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            preparedStatement.setInt(1, 0);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(conn, statement, resultSet);
        }
    }

工具类

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

public class JdbcUtil {
    public  static Connection getConnection() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "123456";
        //8.0之后名字改了  com.mysql.cj.jdbc.Driver
        String driverName = "com.mysql.cj.jdbc.Driver";

        //2.实例化Driver
        Class clazz = Class.forName(driverName);
        Driver driver = (Driver) clazz.newInstance();
        //3.注册驱动
        DriverManager.registerDriver(driver);
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
    public  static Connection getConnection2() throws IOException, SQLException {
    Properties properties = new Properties();
        properties.load(JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
    String url = properties.getProperty("mysql.url");
    String user = properties.getProperty("mysql.username");
    String password = properties.getProperty("mysql.password");
    Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
     }
    public static void close(Connection conn, Statement statement, ResultSet resultSet) throws SQLException {
        if (conn !=null){
            conn.close();
        }
        if (statement !=null){
            statement.close();
        }
        if (resultSet !=null){
            resultSet.close();
        }
    }
    }

四、数据库连接池

Druid（德鲁伊）数据库连接池

   @Test
    public void test() throws IOException, SQLException {
        Properties properties=new Properties();
        properties.load(test.class.getClassLoader().getResourceAsStream("druid.properties"));
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.configFromPropety(properties);

        DruidPooledConnection connection = druidDataSource.getConnection();

        System.out.println(druidDataSource.getCreateCount());
    }

配置文件为：【druid.properties】

druid.username=root
druid.password=123456
druid.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
druid.driverName=com.mysql.cj.jdbc.Driver
druid.keepAlive=300
druid.maxWait=300
druid.maxActive=20
druid.initialSize=10

Hikari（光）数据库连接池

引入四个jar包：

HikariCP-3.4.2.jar
slf4j-api-1.7.29.jar
slf4j-log4j12-1.7.21.jar
log4j-1.2.17.jar

    @Test
    public void test2() throws IOException, SQLException {
        Properties properties=new Properties();
        properties.load(test.class.getClassLoader().getResourceAsStream("Hikari.properties"));
        HikariConfig hikariConfig = new HikariConfig(properties);
        HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);
        Connection connection = hikariDataSource.getConnection();
        System.out.println(connection);
    }

配置文件为：【Hikari.properties】

username=root
password=123456
jdbcUrl=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
driverClassName=com.mysql.cj.jdbc.Driver

五、Dao的运用

简单的

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;
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 BaseDao {
    protected static DataSource DATASOURCE;
    static {
        Properties properties = new Properties();
        try {
            properties.load(test.class.getClassLoader().getResourceAsStream("druid.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        DATASOURCE = new DruidDataSource();
        ((DruidDataSource) DATASOURCE).configFromPropety(properties);
    }

    protected  void close(Connection connection, Statement statement, ResultSet resultSet) {
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao extends BaseDao {
    public int saveUser(User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            String sql = "insert  into user values (?,?,?)";
            connection = DATASOURCE.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, user.getId());
            preparedStatement.setString(2, user.getName());
            preparedStatement.setInt(3, user.getAge());
            int rows = preparedStatement.executeUpdate();
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, null);
        }
        return -1;
    }

    public int deleteUser(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            String sql = "delete  from user where id=?";
            connection = DATASOURCE.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);

            int rows = preparedStatement.executeUpdate();
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, null);
        }
        return -1;
    }

    public int UpdateUser(User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            String sql = "update user set name=?,age=? where id=?";
            connection = DATASOURCE.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getName());
            preparedStatement.setInt(2, user.getAge());
            preparedStatement.setInt(3, user.getId());
            int rows = preparedStatement.executeUpdate();
            return rows;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, null);
        }
        return -1;
    }

    public List<User> selectAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        List<User> users = new ArrayList<>();
        try {
            String sql = "select id,name,age from user";
            connection = DATASOURCE.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                users.add(new User(resultSet.getInt("id"),
                        resultSet.getString("name"),
                        resultSet.getInt("age")));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection, preparedStatement, resultSet);
        }
        return users;
    }

    public static void main(String[] args) {
        UserDao userDao = new UserDao();
        List<User> users = userDao.selectAll();
        System.out.println(users);
    }
}

好用的

import java.util.List;

public interface BetterBaseDao<T> {
    int save(T t);

    int delete(int id);

    List<T> selectAll(Class clazz);

    T selectOne(int id);

    int update(T t);


}

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class BaseDaoImpl<T> implements BetterBaseDao<T>{
    protected static DataSource DATASOURCE;
    static {
        Properties properties = new Properties();
        try {
            properties.load(test.class.getClassLoader().getResourceAsStream("druid.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        DATASOURCE = new DruidDataSource();
        ((DruidDataSource) DATASOURCE).configFromPropety(properties);
    }

    protected  void close(Connection connection, Statement statement, ResultSet resultSet) {
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    @Override
    public int save(T t) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;

        try {
            Object o= t;
            //1.定义sql
            Class<?> clazz = t.getClass();
            String name = clazz.getName().substring(clazz.getName().lastIndexOf(".")+1).toLowerCase();
            Field[] declaredFields = clazz.getDeclaredFields();//所有权限的字段都可以拿到
            StringBuilder sql = new StringBuilder();
            sql.append("insert into").append(name).append("values(");
            for (int i = 0; i < declaredFields.length; i++) {
                sql.append("?,");
            }
            sql.append(")");
            String finalsql = sql.toString().replace(",)", ")");

            //2.获取链接
            connection=DATASOURCE.getConnection();
             preparedStatement = connection.prepareStatement(finalsql);

            //3.prepared设置值
            for (int i=1; i<declaredFields.length;i++) {
                declaredFields[i-1].setAccessible(true);
                if (declaredFields[i-1].getType()==int.class){
                    preparedStatement.setInt(i,declaredFields[i-1].getInt(o));
                }else if(declaredFields[i-1].getType()==String.class){
                    preparedStatement.setString(i,declaredFields[i-1].get(o).toString());
                }
            }
            //4.执行
            int rows = preparedStatement.executeUpdate();
       return rows;
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            close(connection,preparedStatement,null);
        }
        return -1;
    }

    @Override
    public int delete(int id) {
        return 0;
    }

    @Override
    public List<T> selectAll(Class clazz) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet;
        List<T> objs = new ArrayList<>();
        try {
            //1.定义sql
            String name = clazz.getName().substring(clazz.getName().lastIndexOf(".") + 1).toLowerCase();
            Field[] declaredFields = clazz.getDeclaredFields();//所有权限的字段都可以拿到
            StringBuilder sql = new StringBuilder();
            sql.append("select * from ").append(name);

            //2.获取链接
            connection = DATASOURCE.getConnection();
            preparedStatement = connection.prepareStatement(sql.toString());
            //4.执行
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Object object = null;
                try {
                    object = clazz.newInstance();
                } catch (InstantiationException ex) {
                    ex.printStackTrace();
                }
                for (int i = 0; i < declaredFields.length; i++) {
                    declaredFields[i].setAccessible(true);
                    if (declaredFields[i].getType() == int.class) {
                        declaredFields[i].setInt((T) object, resultSet.getInt(declaredFields[i].getName()));
                    } else if (declaredFields[i].getType() == String.class) {
                        declaredFields[i].set((T) object, resultSet.getString(declaredFields[i].getName()));
                    }
                }
                objs.add((T) object);
            }
        } catch (SQLException | IllegalAccessException ex) {
            ex.printStackTrace();
        } finally {
            close(connection, preparedStatement, null);
        }
        return  objs;
    }
    @Override
    public T selectOne(int id) {
        return null;
    }
    @Override
    public int update(T t) {
        return 0;
    }
}

调用

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

public class UserDao2 extends BaseDaoImpl<User> implements BetterBaseDao<User>{

    public static void main(String[] args) {
        UserDao2 userDao2 = new UserDao2();
        List<User> users = userDao2.selectAll(User.class);
        Logger logger = LoggerFactory.getLogger("select");
        logger.error("查询崩溃");
        logger.debug("查询错误");
        System.out.println(users);

    }
}

需要在src下建立一个log4j.properties文件内容，这个文件告诉我们启动连接池的一些日志信息

log4j.rootLogger=debug, stdout,file

# Redirect log messages to console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n

# Rirect log messages to a log file
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=test.log
log4j.appender.file.MaxFileSize=5MB
log4j.appender.file.MaxBackupIndex=10
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss} %-5p %c{1}:%L - %m%n