JDBC的学习

概念

Java database connectivity: Java数据库连接,使用Java来连接不同数据库的一种技术

jdbc API

DriverManager	驱动管理器
Connection	数据库连接对象
Statement	声明,用来执行SQL语句
ResultSet	结果集,查询后的结果集合游标
PreparedStatement	声明,效率更高,支持预编译,防止SQL注入
SQLException	SQL异常,检测异常

开发步骤

整个项目只需做一次

1. 项目中新增一个lib目录
2. 在lib目录中添加mysql对应版本的jar包
3. 将jar包作为仓库添加到当前项目中

每个类中需要做的操作

1. Class.forName();注册驱动
  - mysql5:com.mysql.jdbc.Driver
  - mysql8:com.mysql.cj.jdbc.Driver
2. Connection conn = DriverManager.getConnection(url, user, pass);//使用DriverManager对象的getConnection()方法获取数据库的连接对象
  	1. url代表连接的是哪台主机的哪个端口的哪个数据库
  	2. user以哪个用户来访问
  	3. pass是user的密码
3. String sql = "insert/update/delete   or select";  //创建SQL语句   
4. Statement stmt = conn.createStatement();      //使用sql语句作为参数放在conn的createStatement()方法中创建Statement对象
5. 执行sql语句,
  - stmt.executeUpdate(sql);执行增删改的dml语句,返回的结果为受影响的行数int值
  - stmt.executeQuery(sql);执行查询操作dql语句,返回的是结果集对象ResultSet
6. 关闭资源
  	1. 关闭ResultSet(如果有的话)
  	2. 关闭Statement对象
  	3. 关闭Connection对象

包的层次划分

• dao: data access object ,数据访问对象，跟数据库相关的所有操作都可以放在此包下
• dao.impl: dao的实现类，完成对于dao接口的所有的方法的实现
• entity：实体类所在的包，包中的类要与数据库中的表是对应的，类名与表明对应，属性名与字段名对应
• util：工具包，里面放置各种工具类
• test： 测试，完成对于各个类的测试

jdbc案例

person数据库

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid   | int(11)     | YES  |     | NULL    | 编号   |
| pname | varchar(20) | YES  |     | NULL    | 姓名   |
| ppass | varchar(20) | YES  |     | NULL    | 密码   |
+-------+-------------+------+-----+---------+-------+

文件目录

person.java（Person实体类）

package com.company.entity;

public class Person {
    private int pid;
    private String pname;
    private String ppass;

    public Person() {
    }

    public Person(int pid, String pname, String ppass) {
        this.pid = pid;
        this.pname = pname;
        this.ppass = ppass;
    }

    public int getPid() {
        return pid;
    }

    public void setPid(int pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    public String getPpass() {
        return ppass;
    }

    public void setPpass(String ppass) {
        this.ppass = ppass;
    }

    @Override
    public String toString() {
        return "Person{" +
                "pid=" + pid +
                ", pname='" + pname + '\'' +
                ", ppass='" + ppass + '\'' +
                '}';
    }
}

DBUtils.java（工具类）

package com.company.utils;

import java.sql.*;

/**
 * 数据库连接工具类
 *getConnection()方法获取数据库连接对象，以供其他dao进行连接资源的获取
 * closeConnection()方法关闭数据库连接资源
 */
public class DBUtils {


   private static  Connection connection = null;
   //将注册驱动放在静态代码块中，提高连接速度
    static {
        //通过反射注册驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接对象
     * 返回值为Connection对象
     */
    public static Connection getConnection(){

        try {
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/java22","root","root"
            );
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }
    /**
     * 关闭连接资源
     * rs为结果集对象
     * stmt 声明对象
     * cs  连接对象
     */
    public static void closeConnection(ResultSet rs, Statement stmt,Connection cs){
        try {
            if(rs != null){
                rs.close();
                rs = null;
            }
            if(stmt != null){
                stmt.close();
                stmt = null;
            }
            if(cs != null){
                cs.close();
                cs = null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

IPersonDao.java(接口)

package com.company.dao;

import com.company.entity.Person;

import java.util.List;

public interface IPersonDao {
    /**
     * 分页查询,分页查询所有满足规则的Person对象所组成的List集合
     * @param cp currentPage, 当前第几页
     * @param ps pageSize,每页最多显示几条记录
     * @return 满足分页条件的Person的集合
     */
    List<Person> getPersonByPage(int cp,int ps);
    /**
     * 查询所有的Person对象并封装为一个List集合
     * @return 所有的person对象的集合
     */
     List<Person> getAllPersons();

    /**
     * 根据pid得到一个Person对象
     * @param pid Person的pid
     * @return pid对应的Person对象
     */
    Person getPersonById(int pid);

    /**
     * 向数据库中添加一个新的person对象
     * @param person 新的person对象
     * @return 返回受影响的行数
     */
    int addPerson(Person person);

    /**
     * 在数据库中修改某条数据
     * @param person 修改后的person
     * @return
     */
    int updatePerson(Person person);

    /**
     * 根据id删除某条数据
     * @param pid
     */
    void delPerson(int pid);

}

PersonDaoImpl.java

package com.company.dao.Impl;

import com.company.dao.IPersonDao;
import com.company.entity.Person;
import com.company.utils.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PersonDaoImpl implements IPersonDao {
    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;


    @Override
    public List<Person> getPersonByPage(int cp, int ps) {
        List<Person>  list = null;
        connection = DBUtils.getConnection();


        int si = (cp - 1) * ps;
        String sql = String.format("select * from person limit %d,%d",si,ps);

        try {
            //创建Statement对象
            statement = connection.createStatement();
            //执行sql语句
            resultSet = statement.executeQuery(sql);

            if(resultSet != null) {
                list =new ArrayList<>();
                Person person = null;
                while (resultSet.next()) {
                    //获取到Person对象
                    person = new Person(resultSet.getInt(1), resultSet.getString(2),
                            resultSet.getString(3));
                    //将person对象放入到集合中
                    list.add(person);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.closeConnection(resultSet,statement,connection);
        }

        return list;
    }

    @Override
    public List<Person> getAllPersons() {
        List<Person> list = null;
        //创建连接对象
        connection = DBUtils.getConnection();
        //写sql语句
        String sql = "select * from person";
        try {
            //创建Statement对象
             statement = connection.createStatement();

            //执行sql语句
            resultSet = statement.executeQuery(sql);
            if(resultSet != null) {
                list = new ArrayList<>();
                Person person = null;
                while (resultSet.next()) {
                    person = new Person(resultSet.getInt(1), resultSet.getString(2)
                            , resultSet.getString(3));
                    list.add(person);
                }
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBUtils.closeConnection(resultSet,statement,connection);
        }

        return list;
    }

    @Override
    public Person getPersonById(int pid) {
        Person person = null;
        //获取连接
        connection = DBUtils.getConnection();
        //编写sql语句
        String sql = String.format(" select * from person where pid = %d",pid);

        try {
            //创建Statement对象
            statement = connection.createStatement();
            //执行sql语句
             resultSet = statement.executeQuery(sql);

             while (resultSet.next()){
                 person = new Person(resultSet.getInt(1), resultSet.getString(2)
                         , resultSet.getString(3));
             }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            //关闭所有连接资源
            DBUtils.closeConnection(resultSet,statement,connection);
        }

        return person;
    }

    @Override
    public int addPerson(Person person) {
        //创建连接
        connection = DBUtils.getConnection();
        //编写sql语句
        String sql = String.format(" insert into person(pid,pname,ppass) values(%d,%s,%s)",person.getPid()
                ,person.getPname(),person.getPpass());
        int i = 0;
        try {
            //创建Statement对象
            statement = connection.createStatement();
            //执行sql语句
             i = statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭所有资源
            DBUtils.closeConnection(resultSet,statement,connection);
        }
        return i;
    }

    @Override
    public int updatePerson(Person person) {
        int i = 0;
        connection = DBUtils.getConnection();
        String sql = String.format(" update person set pname='%s',ppass='%s' where pid = %d",
               person.getPname(),person.getPpass(),person.getPid());

        try {
            statement = connection.createStatement();
            i = statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.closeConnection(resultSet,statement,connection);
        }
        return i;
    }

    @Override
    public void delPerson(int pid) {
        connection = DBUtils.getConnection();

        String sql = String.format(" delete from person where pid =%d",pid);

        try {
            statement = connection.createStatement();
            statement.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.closeConnection(resultSet,statement,connection);
        }
    }
}

PersonDaoImplTest.java(测试类)

package com.company.test;


import com.company.dao.IPersonDao;
import com.company.dao.Impl.PersonDaoImpl;
import com.company.entity.Person;
import org.junit.Test;
import java.util.ArrayList;
import java.util.List;

public class PersonDaoImplTest {
    IPersonDao iPersonDao = new PersonDaoImpl();
    List <Person> list = new ArrayList();
    Person person = new Person();
    @Test
    public void getPersonByPage() {
        list = iPersonDao.getPersonByPage(1,5);
        for (Person person : list) {
            System.out.println(person);
        }
    }

    @Test
    public void getAllPersons() {
        list = iPersonDao.getAllPersons();
        for (Person person : list) {
            System.out.println(person);
        }
    }

    @Test
    public void getPersonById() {
        person = iPersonDao.getPersonById(3);
        System.out.println(person);
    }

    @Test
    public void addPerson() {
        person = new Person(10,"9","9");
        iPersonDao.addPerson(person);
        System.out.println("===========");
        list = iPersonDao.getAllPersons();
        for (Person person1 : list) {
            System.out.println(person1);
        }
    }

    @Test
    public void updatePerson() {
        int pid = 9;
        person = new Person(pid,"jack","6666");
        iPersonDao.updatePerson(person);
        list =  iPersonDao.getAllPersons();
        for (Person person1 : list) {
            System.out.println(person1);
        }
    }

    @Test
    public void delPerson() {
        iPersonDao.delPerson(9);
        list = iPersonDao.getAllPersons();
        for (Person person1 : list) {
            System.out.println(person1);
        }
    }
}

sql注入问题

什么是sql注入

用户输入的数据中有SQL关键字或语法并且参与了SQL语句的编译，导致SQL语句编译后的条件含义为true，一直得到正确的结果。这种现象称为SQL注入。

如何避免sql注入

由于编写的 SQL语句是在用户输入数据，整合后再进行编译。所以为了避免SQL注入的问题，我们要使SQL语句在用户输入数据前就已进行编译成完整的SQL语句,再进行填充数据。

PreparedStatement的应用

• 预编译sql语句，效率高
• 安全避免sql注入。
• 可以动态的填充数据，执行多个同构的sql语句

参数标记

//1.预编译SQL 语句
PreparedStatement pstmt = conn.prepareStatement("select ± from user where username=? and password=?");
注意:JDBC中的所有参数都由﹖符号占位，这被称为参数标记。在执行sQL语句之前，必须为每个参数提供值。

动态参数绑定

**pstmt.setXxx(下标,值)**参数下标从1开始，为指定参数下标绑定值

1/1.预编译SQL语句
PreparedStatement pstmt = conn.prepareStatement("select * from user where username=? and password=?")/12.为参数下标赋值
pstmt.setString(1 , username);pstmt.setString(2, password);

JDBC优化(一)

• 优化功能使用PrepareStatement代替Statement解决sql注入的问题
• 在DBUtils工具的基础上创建DaoUtils工具用于操作通用的DML操作和查询操作

login数据库

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| pid   | int(11)     | NO   | PRI | NULL    |       |
| pname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Login实体类

package com.company2.entity;

public class Login {
    private int pid;
    private String pname;

    public Login() {
    }

    public Login(int pid, String pname) {
        this.pid = pid;
        this.pname = pname;
    }

    public int getPid() {
        return pid;
    }

    public void setPid(int pid) {
        this.pid = pid;
    }

    public String getPname() {
        return pname;
    }

    public void setPname(String pname) {
        this.pname = pname;
    }

    @Override
    public String toString() {
        return "Login{" +
                "pid=" + pid +
                ", pname=" + pname +
                '}';
    }
}

工具类

DBUtils.java(连接工具)

package com.company2.utils;

import java.sql.*;

public class DBUtils {
    private static Connection connection = null;

    private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    private static final String DB_URL = "jdbc:mysql://localhost:3306/java22";
    private static final String DB_USER = "admin";
    private static final String DB_PASSWORD = "root";

    static {
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() {
        try {
            connection = DriverManager.getConnection(DB_URL,DB_USER,DB_PASSWORD);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }
    //关闭所有资源
    public static void closeAll(ResultSet rs, Statement stmt,Connection ct){
        try {
            if(rs != null){
                rs.close();
                rs = null;
            }
            if(stmt != null){
                stmt.close();
                stmt = null;
            }
            if(ct != null){
                ct.close();
                ct = null;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}

DaoUtils.java（通用的DML和查询操作工具）

package com.company2.utils;
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 DaoUtils<T> {
    private static ResultSet rs = null;
    private static PreparedStatement psmt = null;
    private static Connection conn = null;

    //增删改功能
    public static  int update(String sql,Object...params){
        int result = 0;
        conn = DBUtils.getConnection();

        try {
            psmt =conn.prepareStatement(sql);

            for (int i = 0; i < params.length; i++) {
                Object param = params[i];
                psmt.setObject(i+1,param);
            }

            result = psmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBUtils.closeAll(rs,psmt,conn);
        }

        return result;
    }

    //查询功能 泛型不能用静态变量
        public  List<T> query(String sql, RowMapper<T> rowMapper, Object...params){
            List<T> list = null;

            conn = DBUtils.getConnection();

            try {
                psmt = conn.prepareStatement(sql);

                for (int i = 0; i < params.length; i++) {
                    Object p = params[i];
                    psmt.setObject(i + 1, p);
                }

                rs = psmt.executeQuery();
                list = new ArrayList<>();//注意list不能放入while中否则在测试时list中始终只有一条数据。
                while (rs.next()){

                    T t = rowMapper.getRow(rs);
                    list.add(t);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                DBUtils.closeAll(rs,psmt,conn);
            }

        return  list;
        }
}

DML操作

ILoginDao.java（接口）

package com.company.dao;

import com.company.entity.Person;

import java.util.List;

public interface IPersonDao {
    
 
    
    /**
     * 分页查询,分页查询所有满足规则的Person对象所组成的List集合
     * @param cp currentPage, 当前第几页
     * @param ps pageSize,每页最多显示几条记录
     * @return 满足分页条件的Person的集合

    List<Person> getPersonByPage(int cp,int ps);

     * 查询所有的Person对象并封装为一个List集合
     * @return 所有的person对象的集合
    
     List<Person> getAllPersons();

  
     * 根据pid得到一个Person对象
     * @param pid Person的pid
     * @return pid对应的Person对象
   
    Person getPersonById(int pid);
    
    */
    

    /**
     * 向数据库中添加一个新的person对象
     * @param person 新的person对象
     * @return 返回受影响的行数
     */
    int addPerson(Person person);

    /**
     * 在数据库中修改某条数据
     * @param person 修改后的person
     * @return
     */
    int updatePerson(Person person);

    /**
     * 根据id删除某条数据
     * @param pid
     */
    void delPerson(int pid);

}

查询操作

RowMapper.java(接口)

package com.company2.utils;

import java.sql.ResultSet;

//将ResultSet转换为T
public interface RowMapper<T> {
    T getRow(ResultSet rs);
}

RowMapperImpl.java

package com.company2.utils;

import com.company2.entity.Login;

import java.sql.ResultSet;
import java.sql.SQLException;
//接口实现类 T为login 可插拔式  将rs转换为Login对象  在LoginDaoImpl中作为参数传递在resultSet结果集中将rs转换为Login最后添加到list集合中

public class RowMapperImpl implements RowMapper<Login>{

    @Override
    public Login getRow(ResultSet rs) {
        Login login = new Login();

        try {
            login.setPid(rs.getInt(1));
            login.setPname(rs.getString(2));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return login;
    }
}

ILoginDao.java（接口）

package com.company.dao;

import com.company.entity.Person;

import java.util.List;

public interface IPersonDao {
    
 

    List<Person> getPersonByPage(int cp,int ps);

    
     List<Person> getAllPersons();

 
   
    Person getPersonById(int pid);
    
  
    

    /**
     * 向数据库中添加一个新的person对象
     * @param person 新的person对象
     * @return 返回受影响的行数
 
    int addPerson(Person person);

  
     * 在数据库中修改某条数据
     * @param person 修改后的person
     * @return
     
    int updatePerson(Person person);


     * 根据id删除某条数据
     * @param pid

    void delPerson(int pid);
    */

}

接口实现类

LoginDaoImpl.java

package com.company2.dao.daoImpl;

import com.company2.dao.ILoginDao;
import com.company2.entity.Login;
import com.company2.utils.DaoUtils;
import com.company2.utils.RowMapperImpl;

import java.util.List;

public class LoginDaoImpl implements ILoginDao {
    //DML操作
    @Override
    public int saveLogin(Login login) {
        String sql = " insert into login values(?,?)";
        int result = DaoUtils.update(sql,login.getPid(),login.getPname());
        return result;
    }

    @Override
    public int updateLogin(Login login) {
        String sql = "update login set pname = ? where pid = ?";
        int result = DaoUtils.update(sql,login.getPname(),login.getPid());
        return result;
    }

    @Override
    public void delLogin(int pid) {
        String sql = " delete from login where pid = ?";
        int result = DaoUtils.update(sql,pid);
    }


    //查询操作

    @Override
    public Login queryById(int pid) {
        String sql = " select * from login where pid = ?";
        DaoUtils<Login> daoUtils = new DaoUtils<>();
        List<Login> list = daoUtils.query(sql,new RowMapperImpl(),pid);
        return list.get(0);
    }

    @Override
    public List<Login> queryAll() {
        String sql = " select * from login";
        DaoUtils<Login> daoUtils = new DaoUtils<>();
        List<Login> list = daoUtils.query(sql,new RowMapperImpl());
        return list;
    }

    @Override
    public List<Login> queryByPage(int cp,int ps) {
        String sql = " select * from login limit ?,?";
        int si =(cp - 1)* ps;
        DaoUtils<Login> daoUtils = new DaoUtils<>();
        List<Login> list = daoUtils.query(sql,new RowMapperImpl(),si,ps);
        return list;

    }

}

测试类

LoginDaoImplTest.java

package com.company2.test;


import com.company2.dao.daoImpl.LoginDaoImpl;
import com.company2.entity.Login;
import org.junit.Test;

import java.util.List;

public class LoginDaoImplTest {

    @Test
    public void saveLogin() {
        LoginDaoImpl loginDao = new LoginDaoImpl();
        Login login = new Login(6, "56655");
        loginDao.saveLogin(login);
    }

    @Test
    public void updateLogin() {
        LoginDaoImpl loginDao = new LoginDaoImpl();
        Login login = new Login(4, "44444");
        loginDao.updateLogin(login);
    }

    @Test
    public void delLogin() {
        LoginDaoImpl loginDao = new LoginDaoImpl();
        loginDao.delLogin(2);
    }

    //查询测试

    @Test
    public void queryById() {
        LoginDaoImpl loginDao = new LoginDaoImpl();
        Login login = loginDao.queryById(1);
        System.out.println(login);
    }

    @Test
    public void queryAll() {
        LoginDaoImpl loginDao = new LoginDaoImpl();
        List<Login> list   = loginDao.queryAll();
        for (Login login : list) {
            System.out.println(login);
        }
    }

    @Test
    public void queryByPage() {
        LoginDaoImpl loginDao = new LoginDaoImpl();
        List<Login> list   = loginDao.queryByPage(1,3);
        for (Login login : list) {
            System.out.println(login);
        }
    }
}

JDBC优化（Druid）

• 需要有三个jar包放到lib目录中导入库中
  • commons-dbutis-1.6jar 数据库操作工具jar包
  • druid-1.0.28.jar druid数据库连接池jar包
  • mysql-connector-java-5.08-bin.jar 数据库连接jar包
• 需要druid.properties配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java22
user=admin
pass=root

Env.java(以单例设计的模式动态的获取properties文件的信息）

package com.DruidDBUtils;

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

/**
 * Created by .
 *  单例模式创建Env类用来加载db.properties文件,
 *      可以更好的实现跨平台功能
 *      当前类继承Properties,实现properties文件的加载和读取功能
 */
public class Env extends Properties {

    private static Env instance = null;

    private Env(){
        try {
            //  加载db.properties文件
            load(Env.class.getResourceAsStream("/druid.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 公有静态的返回值为当前实例的方法
     * @return
     */
    public static Env getInstance(){
        if(instance == null)
            instance = new Env();

        return instance;
    }
}

JDBCUtilByDruid2.java（Druid工具类）

数据库连接池在内部进行关闭

package com.DruidDBUtils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.util.Properties;

public class JDBCUtilByDruid2 {
    private static DruidDataSource dataSource = null;
    //加载配置文件
    static {
        Properties p = new Properties();
        try {
            p.load(JDBCUtilByDruid2.class.getResourceAsStream("/druid.properties"));

//将配置文件转换为DataSource
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(p);
            
            //通过Env类（因为继承了Properties类所以具有加载读取功能）进行读取信息给DataSource

            //当Properties中的key与druid中的key相同就不需要Env类，不用执行以下语句就已经在类加载中完成文件读取使用Env类是在Properties中的key与druid中的key不相同的情况下，需要在Env类中类加载一次后Env.getInstance().getProperty("XXXX")得到后JDBCUtilByDruid2.class.getResourceAsStream("/druid.properties")加载一次设置 dataSource.setUrl()提供类加载使用。
            dataSource.setUrl(Env.getInstance().getProperty("url"));
           dataSource.setDriverClassName(Env.getInstance().getProperty("driver"));
            dataSource.setUsername(Env.getInstance().getProperty("user"));
            dataSource.setPassword(Env.getInstance().getProperty("pass"));

        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库数据源对象
     * @return 数据源对象
     */
    public static DataSource getDataSource(){
        return dataSource;
    }

}

LoginDaoImplByDruid2.java（接口实现类）

• 使用QueryRunner（commons-dbutis-1.6jar)工具进行DML和查询操作
• 其中BeanListHandler Handler和JDBC优化（一）中的mapper具有相同作用

package com.company2.dao.daoImpl;

import com.DruidDBUtils.JDBCUtilByDruid2;
import com.company2.dao.ILoginDao;
import com.company2.entity.Login;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

public class LoginDaoImplByDruid2 implements ILoginDao {

    private QueryRunner queryRunner =new QueryRunner(JDBCUtilByDruid2.getDataSource());
    @Override
    public int saveLogin(Login login) {

        try {
            return queryRunner.update("insert into login values(?,?)", login.getPid(),login.getPname());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
     return 0;
    }

    @Override
    public int updateLogin(Login login) {
        try {
            return queryRunner.update("update login set pname = ? where pid = ?",login.getPname(),login.getPid());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public void delLogin(int pid) {

        try {
            queryRunner.update("delete from login where pid = ?",pid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public Login queryById(int pid) {

        try {
           return queryRunner.query(" delete from login where pid = ?",new BeanListHandler<Login>(Login.class)).get(0);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Login> queryAll() {
        try {
            return queryRunner.query("select * from person", new BeanListHandler<Login>(Login.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Login> queryByPage(int cp, int ps) {

        try {
            return queryRunner.query("select * from person  limit ?,?", new BeanListHandler<Login>(Login.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}

LoginDaoImplByDruid2Test.java(测试类)

package com.company2.test;


import com.company2.dao.daoImpl.LoginDaoImpl;
import com.company2.dao.daoImpl.LoginDaoImplByDruid2;
import com.company2.entity.Login;
import org.junit.Test;

import java.util.List;



public class LoginDaoImplByDruid2Test {

    @Test
    public void saveLogin() {
        LoginDaoImplByDruid2 loginDao = new LoginDaoImplByDruid2();
        Login login = new Login(10, "666666");
        loginDao.saveLogin(login);
    }

    @Test
    public void updateLogin() {
        LoginDaoImplByDruid2 loginDao = new LoginDaoImplByDruid2();
        Login login = new Login(9, "8888777777");
        loginDao.updateLogin(login);
    }

    @Test
    public void delLogin() {
        LoginDaoImpl loginDao = new LoginDaoImpl();
        loginDao.delLogin(10);
    }

    //查询测试

    @Test
    public void queryById() {
        LoginDaoImpl loginDao = new LoginDaoImpl();
        Login login = loginDao.queryById(1);
        System.out.println(login);
    }

    @Test
    public void queryAll() {
        LoginDaoImpl loginDao = new LoginDaoImpl();
        List<Login> list   = loginDao.queryAll();
        for (Login login : list) {
            System.out.println(login);
        }
    }

    @Test
    public void queryByPage() {
        LoginDaoImpl loginDao = new LoginDaoImpl();
        List<Login> list   = loginDao.queryByPage(1,3);
        for (Login login : list) {
            System.out.println(login);
        }
    }


}