从jdbc和德鲁伊开始

其他随笔

1. jdbc idea新建一个普通maven模块jdbctest 引入依赖

   <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>      
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>

新建一个jdbclearn类 做一个test 做一个读操作，别的也就差不多了

 @Test
    public void ReadTest() throws Exception  {
        Connection con=null;
        Statement stat=null;
        try {
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1/guantest", "root", "1234");
            stat = con.createStatement();
            ResultSet rs = stat.executeQuery("select * from sys_user");

            while (rs.next()) {
                System.out.println("编号"+rs.getString("code") +"id"+ rs.getInt("id"));
            }
        } finally {
            if(stat!=null)
                stat.close();
            if (con!=null)
                con.close();
        }
    }

结果

编号001id1
编号002id2
编号003id3

这里我们可以封装一下 写个mysqlUtils 毕竟这样稍显啰嗦

再resources中新建一个config.properties

conStr=jdbc:mysql://127.0.0.1/guantest
user=root
pwd  =1234

好了先随便封装个，毕竟真正做项目很少用到jdbc,这里还是只对读做封装，当然写的话会更简单一些

package com.guan;

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

public class mysqlUtils {

  private  String conStr="jdbc:mysql://127.0.0.1/guantest";
    private  String user="root";
    private  String pwd="1234";
   
    Connection con =null;
    Statement stat=null;


    public mysqlUtils() throws Exception {
        ini();
    }

    public ResultSet ReadData(String sql) throws SQLException {
        Statement  stat = con.createStatement();
        ResultSet rs = stat.executeQuery(sql);
        return rs;
    }

    public void close() throws Exception{
        if (  stat!=null)
            stat.close();
       if ( con !=null)
           con.close();
    }


    public void ini() throws Exception {
        Properties prop = new Properties();

        String path = System.getProperty("user.dir");
        //FileInputStream fis = new FileInputStream(path+"\\src\\main\\resources\\config.properties");
        FileInputStream fis = new FileInputStream( mysqlUtils.class.getResource("/").getPath()+"\\config.properties");

        prop.load(fis);
        // 获取数据库相关信息
        conStr  = prop.getProperty("conStr");
        user  = prop.getProperty("user");
        pwd  = prop.getProperty("pwd");
         con = DriverManager.getConnection(conStr, user, pwd);
    }   
}

测试一下

   @Test
    public void ReadTest2() throws Exception  {
        Connection con=null;
        Statement stat=null;


        mysqlUtils u =new mysqlUtils();

            //读用户
            ResultSet rs = u.ReadData("select * from sys_user");
            while (rs.next()) {
                System.out.println("编号："+rs.getString("code") +"，id："+ rs.getInt("id"));
            }

            //读部门
             rs =  u.ReadData("select * from sys_department");
            while (rs.next()) {
                System.out.println("名称："+rs.getString("name") +"，id："+ rs.getInt("id"));
            }

            rs.close();
            u.close();

    }

结果

 2. 德鲁伊

依赖中添加如下

   <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.20</version>
        </dependency>

 

recources中新建一个druid.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1/guantest?useSSL=false&useServerPrepStmts=true
username=root
password=1234
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

包mysqlUtils复制改用成mysqldlyUtils 改成如下：

package com.guan;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.*;
import java.util.Properties;

public class mysqldlyUtils {

    Connection con =null;
    Statement stat = null;

    public mysqldlyUtils() throws Exception {
        ini();
    }


    public ResultSet ReadData(String sql) throws SQLException {
        Statement stat = con.createStatement();
        ResultSet rs = stat.executeQuery(sql);
        return rs;
    }

    public void close() throws Exception {
        if (stat!=null)
            stat.close();
        if (con != null)
            con.close();
    }

    public void ini() throws Exception {
        Properties prop = new Properties();
        FileInputStream fis = new FileInputStream(mysqldlyUtils.class.getResource("/").getPath() + "\\druid.properties");
        prop.load(fis);
        con = DruidDataSourceFactory.createDataSource(prop).getConnection();
    }

}

测试

 @Test
    public void druidTest() throws Exception {
        //加载配置文件
       mysqldlyUtils u=new mysqldlyUtils();

        ResultSet rs = u.ReadData("select * from sys_user");

        while (rs.next()) {
            System.out.println("编号"+rs.getString("code") +"id"+ rs.getInt("id"));
        }
        rs.close();
        u.close();

    }

结果