java全栈学习之路——jdbc(1).md

jdbc

jdbc连接数据库

String className = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://主机ip:3306/数据库名?rewriteBatchedStatements=true&useSSL=false";
String user = "root";
String password = "12345";
connection = null;
try {
//加载注册驱动
Class.forName(className);
//获得一个Connection对象
connection = DriverManager.getConnection(url, user, password);
//数据库操作方法
saveBookToSql();
connection.close();
} catch (Exception e) {
logger.error("数据库驱动加载失败：" + e.getMessage());
}

连接完成可以在mysql中通过show processlist; 查看线程是否连接成功。

一般数据库连接都会用一个JdbcUtil类封装。

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

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

/**
 * Description:
 * Date: 21:38 2019/10/13
 *
 * @author yong
 * @see
 */
public class JdbcUtil {
    private static final Logger logger = LoggerFactory.getLogger(JdbcUtil.class);
    private static Properties p;

    //静态代码块 防止多次加载驱动减小开销
    static {
        p = new Properties();
        try {
            ClassLoader c = Thread.currentThread().getContextClassLoader();
            InputStream inputStream = c.getResourceAsStream("db.properties");
            if (inputStream == null) {
                throw new FileNotFoundException("db.properties文件未找到");
            }
            p.load(inputStream);
        } catch (IOException e) {
            throw new RuntimeException("db.properties读取失败", e);
        }
        try {
            Class.forName(p.getProperty("driverClassName"));
        } catch (Exception e) {
            throw new RuntimeException("数据库驱动加载失败", e);
        }
    }

    /**
     * 获取数据库连接对象
     *
     * @return
     */
    public static Connection getConnection() {
        Connection connection;
        try {
            connection = DriverManager.getConnection(
                    p.getProperty("url"),
                    p.getProperty("name"),
                    p.getProperty("password"));
            return connection;
        } catch (SQLException e) {
            throw new RuntimeException("数据库连接失败", e);
        }
    }

    public static void close(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                logger.error("数据库关闭失败", e);
            }
        }
    }
}

jdbc操作数据库

1. 插入（因为增删改除了sql语句有区别外其他都一样，所以只举插入操作）

    @Test
    public void insert() {
        String sql = "insert into `text`(`name`) values (?)";
        Connection connection;
        //获取连接对象
        connection = JdbcUtil.getConnection();
        //获取
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "小刘");
            //受影响行数
            int res = preparedStatement.executeUpdate();
            preparedStatement.close();
            JdbcUtil.close(connection);
            System.out.println(res);
        } catch (SQLException e) {
            throw new RuntimeException("数据插入失败", e);
        }
    }

2. 查询

    @Test
    public void select() {
        String sql = "select id,`name` from `text` where `name` = ?";
        Connection connection;
        //获取连接对象
        connection = JdbcUtil.getConnection();
        //获取
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "小刘");
            //查询的结果集
            ResultSet res = preparedStatement.executeQuery();
            while (res.next()) {
                System.out.println("id:" + res.getInt("id") + ",name:" + res.getString("name"));
            }
            //关闭结果集
            res.close();
            preparedStatement.close();
            JdbcUtil.close(connection);
        } catch (SQLException e) {
            throw new RuntimeException("数据查询失败", e);
        }
    }