2019.8.26 NIIT第四十一天

今日内容概述

连接池
DBUtils工具

MySQL数据库

数据库的概念：管理数据库
mysql：免费，轻量级
数据库文件(idbate)--数据库管理系统(mysql)--操作数据库的软件(cmd/navicat/sqlyog/java)
my.ini--数据库的配置文件(字符集、端口号、数据存储路径，性能优化)--在数据库的安装路径下
DDL--数据库定义语言 CREATE 创建 ALTER 修改 DROP删除
DQL--数据查询语言 select 查询
DML--数据库操作语言 INSERT 插入 UPDATE 更新 DELETE 删除
DCL--数据库控制语言 GRANT ON TO 给权限 REMOVE 删除权限

insert into table (lie) values (zhi)
update table set (lie)=(zhi) where 条件语句
delete from table where 条件语句
select (lie) from table where 全局条件 order by 排序 group by 分组字段 having 分组字段 limit offset length (显示长度)

约束

主键 primary key
自增 auto_increment
非空 not null
唯一 unique
外键 foreign key

表之间的关系

一对一一对多(重点) 多对多(中间表)

多表查询

笛卡尔积(结果为多个表行数的积)
内连接
外连接
- 左外连接 left join 左表所有数据和右表与之匹配的数据公共数据部分会显示两次
- 右外连接 rigth join 右表所有数据和左表与之匹配的数据，公共数据部分会显示两次
- 满外连接 left outer join union 两张表所有数据公共部分只显示一次

子查询

先写子查询
然后再写主查询
any all in

java连接数据库

操作步骤

把连接数据库的驱动jar包导入进项目
利用反射获得驱动
建立连接 Connection conn=DriverManager.getConnection(URL) url="jdbc:mysql://localhost:3306/mydb?user=root&password=root"
获取sql执行器 Statement st=conn.Creatstatement();
执行sql返回结果 ResultSet rs=st.executeQuery(sql);
遍历结果集 rs.next; rs,gexxx();
关闭连接 st.close conn.close

预编译

PreparedStatement 可以防止sql注入: 就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

连接池

使用连接池可以极大的提高程序运行效率

连接池概述实际开发中“获得连接”或“释放资源”是非常消耗系统资源的两个过程，为了解决此类性能问题，通常情况我们采用连接池技术，来共享连接Connection。这样我们就不需要每次都创建连接、释放连接了，这些操作都交给了连接池
概念用池来管理Connection，这样可以重复使用Connection。有了池，所以我们就不用自己来创建Connection，而是通过池来获取Connection对象。当使用完Connection后，调用Connection的close()方法也不会真的关闭Connection，而是把Connection“归还”给池。池就可以再利用这个Connection对象了。
规范 Java为数据库连接池提供了公共的接口：javax.sql.DataSource，各个厂商需要让自己的连接池实现这个接口。这样应用程序可以方便的切换不同厂商的连接池！常见的连接池：DBCP、C3P0。接下来，我们就详细的学习连接池。

package com.igeek1;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author jerryHe
 * @create 2019-08-26 9:45
 */
public class TestPoolConnection {
    @Test
    public void m1() throws SQLException {
        //1.创建连接池
        DruidDataSource dataSource = new DruidDataSource();
        //2.设置连接参数
        //驱动名称
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        //url
        dataSource.setUrl("jdbc:mysql://localhost:3306/mydb");
        //user
        dataSource.setUsername("root");
        //pwd
        dataSource.setPassword("root");
        //initialSize初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次getConnection时
        dataSource.setInitialSize(5);
        //maxActive 最大连接池数量
        dataSource.setMaxActive(20);
        //maxWait 获取连接时最大等待时间，单位毫秒
        dataSource.setMaxWait(5000);
        //设置好参数后，连接池暂时还是空的
        System.out.println(dataSource);

        //从连接池中获取连接
        DruidPooledConnection conn = dataSource.getConnection();
        System.out.println(conn);
        System.out.println(dataSource);

        PreparedStatement ps = conn.prepareStatement("select * from category");
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            System.out.println(rs.getInt(1)+"\t"+rs.getString(2));
        }
    }
}

使用属性文件配置连接池信息

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb
username=root
password=root
maxActive=20
initialSize=10
maxWait=10000

Java代码

package com.igeek1;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author jerryHe
 * @create 2019-08-26 9:45
 */
public class TestPoolConnection1 {
    @Test
    public void m1() throws SQLException, IOException {
       //1.从属性文件中获取连接信息
        Properties ps = new Properties();
        ps.load(this.getClass().getClassLoader().getResourceAsStream("db.properties"));
        //2.把属性文件中的信息设置到连接池中
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(ps.getProperty("driverClassName"));
        dataSource.setUrl(ps.getProperty("url"));
        dataSource.setUsername(ps.getProperty("username"));
        dataSource.setPassword(ps.getProperty("password"));
        dataSource.setMaxActive(Integer.parseInt(ps.getProperty("maxActive")));
        dataSource.setMaxWait(Integer.parseInt(ps.getProperty("maxWait")));
        dataSource.setInitialSize(Integer.parseInt(ps.getProperty("initialSize")));


        //从连接池中获取连接
        DruidPooledConnection conn = dataSource.getConnection();
        System.out.println(conn);
        System.out.println(dataSource);

        PreparedStatement pst = conn.prepareStatement("select * from category");
        ResultSet rs = pst.executeQuery();
        while(rs.next()){
            System.out.println(rs.getInt(1)+"\t"+rs.getString(2));
        }

        pst.close();
        dataSource.close();
    }
}

连接池工具类

package com.igeek.comm;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author jerryHe
 * @create 2019-08-26 10:17
 */
public class JDBCTools {

    private static   DruidDataSource dataSource;

    //1.获取连接池
    public static DataSource getDataSource(){
        init();
        return  dataSource;
    }


    //2.获取连接
    public static Connection getConnection(){
        init();
        try {
            return  dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //3.关闭连接
    public static void closeConn(Connection conn, Statement st){
        try {
            if(conn!=null){
                if(st!=null){
                    st.close();
                }
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    //加载配置文件
    public static void init(){
        try {
            //1.从属性文件中获取连接信息
            Properties ps = new Properties();
            ps.load(new FileInputStream("db.properties"));
            dataSource = new DruidDataSource();
            dataSource.setDriverClassName(ps.getProperty("driverClassName"));
            dataSource.setUrl(ps.getProperty("url"));
            dataSource.setUsername(ps.getProperty("username"));
            dataSource.setPassword(ps.getProperty("password"));
            dataSource.setMaxActive(Integer.parseInt(ps.getProperty("maxActive")));
            dataSource.setMaxWait(Integer.parseInt(ps.getProperty("maxWait")));
            dataSource.setInitialSize(Integer.parseInt(ps.getProperty("initialSize")));
        }catch (Exception e){
            e.printStackTrace();
        }

    }

}

测试类

package com.igeek.comm;

import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author jerryHe
 * @create 2019-08-26 10:26
 */
public class TestConn {
    @Test
    public void m1() throws SQLException {


        Connection conn = JDBCTools.getDataSource().getConnection();

        PreparedStatement pst = conn.prepareStatement("select * from category");
        ResultSet rs = pst.executeQuery();
        System.out.println(conn);
        while(rs.next()){
            System.out.println(rs.getInt(1)+"\t"+rs.getString(2));
        }

        JDBCTools.closeConn(conn,pst);
        System.out.println(conn);
        conn = JDBCTools.getDataSource().getConnection();

        pst = conn.prepareStatement("select * from category");
        rs = pst.executeQuery();
        System.out.println(conn);
        while(rs.next()){
            System.out.println(rs.getInt(1)+"\t"+rs.getString(2));
        }
        JDBCTools.closeConn(conn,pst);
        System.out.println(conn);

    }
    @Test
    public void m2() throws SQLException{
        Connection conn = JDBCTools.getConnection();
        PreparedStatement pst = conn.prepareStatement("select * from category");
        ResultSet rs = pst.executeQuery();
        while(rs.next()){
            System.out.println(rs.getInt(1)+"\t"+rs.getString(2));
        }
        JDBCTools.closeConn(conn,pst);
    }
}

DBUtils工具类

概述 DBUtils是java编程中的数据库操作实用工具，小巧简单实用。 DBUtils封装了对JDBC的操作，简化了JDBC操作，可以少写代码。 Dbutils三个核心功能介绍  QueryRunner中提供对sql语句操作的API.  ResultSetHandler接口，用于定义select操作后，怎样封装结果集.  DbUtils类，它就是一个工具类,定义了关闭资源与事务处理的方法

可以将结果集中的内容分装成对象，然后加入到集合当中，这样就可使得一个表对应一个集合

利用工具类查询一条语句

@Test
    public void m1(){
        QueryRunner qr=new QueryRunner(JDBCTools.getDataSource());
        String sql="select * from product where pid=10";
        try {
            Product p=qr.query(sql,new BeanHandler<>(Product.class));
            System.out.println(p);
            //工具类帮助自动关闭
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

ResultSet rs=st.executeQuery(sql); ResultSetMetaDate rsmd= rs.getMetaDate();可以获取表的结构信息

总结：

javaBean中的属性必须要和数据表的字段一一对应，如果不对应，可以通过修改SQL语句来解决 as
javaBean中的空构造方法必须有，dbutils中封装结果集时，默认调用的是空构造方法
javaBean的属性类型要与数据表中类型一致
javaBean的属性的首字母不要大写

dbutils使用总结：

java bean中的属性必须与数据表字段一一对应，如果不对应，sql语句中把字段用as重命名
Java bean中空的构造方法必须有，dbutils中封装结果集时，默认调用的是空构造方法
Java bean中字段的类型必须与数据表字段类型一一对应

3.3.2 提供连接

 构造方法
 QueryRunner() 创建核心类，没有提供数据源，在进行具体操作时，需要手动提供Connection
 普通方法
 update(Connection conn , String sql , Object ... params) 使用提供的Connection，完成DML语句
 query(Connection conn , String sql , ResultSetHandler , Object ... params) 使用提供的Connection，执行DQL语句，并将查询结果封装到对象中。

QueryRunner的两种构造方法

package com.igeek2;

import com.igeek.comm.JDBCTools;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import java.sql.SQLException;
import java.util.List;

/**
 * @author jerryHe
 * @create 2019-08-26 16:18
 */
public class TestProduct2 {
    String sql = "";
    @Test
    public void m1() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCTools.getDataSource());
        sql="select * from product";
        List<Product> data = qr.query(sql,new BeanListHandler<>(Product.class));
    }

    @Test
    public void m2() throws SQLException {
        QueryRunner qr = new QueryRunner();
        sql="select * from product";
        List<Product> data = qr.query(JDBCTools.getConnection(),sql,new BeanListHandler<>(Product.class));
    }
}

查询参数

 @Test
    public void m3() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCTools.getDataSource());
        sql="select * from product WHERE pid > ? and price > ?";
        //	query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)
        // Object... params
        List<Product> data = qr.query(sql,new BeanListHandler<>(Product.class),5,1000);
        for (Product p:data){
            System.out.println(p);
        }
    }

    @Test
    public void m4() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCTools.getDataSource());
        sql="select * from product WHERE pid > ? and price > ?";
        //	query(Connection conn, String sql, ResultSetHandler<T> rsh, Object... params)
        // Object... params
        Object[] params = {5,1000};
        List<Product> data = qr.query(sql,new BeanListHandler<>(Product.class),params);
        for (Product p:data){
            System.out.println(p);
        }
    }

增删改

@Test
    public void m5() throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCTools.getDataSource());
        sql="INSERT INTO PRODUCT VALUES (NULL,?,?,?)";
        Object[] params = {"华为p30",8888,"c005"};
        int i =qr.update(sql,params);
        System.out.println(i);
    }

所有结果集用法

package com.igeek2;

import com.igeek.comm.JDBCTools;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * @author jerryHe
 * @create 2019-08-26 16:37
 */
public class TestProduct3 {
    String sql = "";
    QueryRunner qr = new QueryRunner(JDBCTools.getDataSource());
    @Test
    public void m1() throws SQLException {
        sql = "select avg(price),count(*) from product";
        Object query = qr.query(sql, new ScalarHandler<>());
        System.out.println(query);
    }

    @Test
    public void m2() throws SQLException {
        sql = "select * from product where pid = 1";
        Map<String, Object> query = qr.query(sql, new MapHandler());
        System.out.println(query);
    }

    @Test
    public void m3() throws SQLException {
        sql = "select * from product";
        List<Map<String, Object>> query = qr.query(sql, new MapListHandler());
        for(Map<String,Object> map:query){
            System.out.println(map);
        }
    }

    @Test
    public void m4() throws SQLException {
        sql = "select * from product where pid = 1";
        Object[] query = qr.query(sql, new ArrayHandler());
        System.out.println(Arrays.toString(query));
    }

    @Test
    public void m5() throws SQLException {
        sql = "select * from product ";
        List<Object[]> query = qr.query(sql, new ArrayListHandler());
        for(Object[] o :query){
            System.out.println(Arrays.toString(o));
        }
    }


    @Test
    public void m6() throws SQLException {
        sql = "select * from product ";
        Map<Object, Map<String, Object>> query = qr.query(sql, new KeyedHandler<>());
        for(Map.Entry<Object,Map<String,Object>> entry : query.entrySet()){
            System.out.println(entry.getKey());
            System.out.println(entry.getValue());
        }
    }

    @Test
    public void m7() throws SQLException {
        sql = "select pname,price from product ";
        List<Object> query = qr.query(sql, new ColumnListHandler<>());
        for (Object o : query){
            System.out.println(o);
        }
    }

}

结果集

方法名	备注
ArrayHandler	将结果集中的第一条记录封装到一个Object[]数组中，数组中的每一个元素就是这条记录中的每一个字段的值
ArrayListHandler	将结果集中的每一条记录都封装到一个Object[]数组中，将这些数组在封装到List集合中。
BeanHandler	将结果集中第一条记录封装到一个指定的javaBean中。
BeanListHandler	将结果集中每一条记录封装到指定的javaBean中，将这些javaBean在封装到List集合中
ColumnListHandler	将结果集中指定的列的字段值，封装到一个List集合中
KeyedHandler	将结果集中每一条记录封装到Map<String,Object>,在将这个map集合做为另一个Map的value,另一个Map集合的key是指定的字段的值。
MapHandler	将结果集中第一条记录封装到了Map<String,Object>集合中，key就是字段名称，value就是字段值
MapListHandler	将结果集中每一条记录封装到了Map<String,Object>集合中，key就是字段名称，value就是字段值，在将这些Map封装到List集合中。
ScalarHandler	它是用于单数据。例如select count(*) from 表操作。

学习java—第四十一天学习笔记