java中登录页面连接数据库 java连接数据库dao层

概述

DAO模式
DAO（Data Access Object）数据访问对象。主要的功能就是用于进行数据操作的，在程序的标准开发架构中属于数据层的操作.

一、针对操作数据库表的编写流程

1、声明一个连接工具类

package com.ruqi.basedao;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class ConnectUtil {

    public static Connection getConnection() throws Exception {
        // 获取链接
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("mysql.propries");
        Properties pros = new Properties();
        pros.load(is);
        String url = pros.getProperty("url");
        String user = pros.getProperty("user");
        String password = pros.getProperty("password");
        String driver = pros.getProperty("driver");
        //加载驱动
        Class.forName(driver);
        //获取连接
        Connection conn =  DriverManager.getConnection(url,user,password);
        return conn;
    }

    public static void closeConnection(Connection conn, PreparedStatement ps, ResultSet rs){
        if(ps!=null){
            try {
                ps.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }if(rs!=null){
            try {
                rs.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

}

2、创建数据库表的对象类，比如表为Scores

package com.ruqi.basedao;
import java.sql.Date;

public class Scores {
    private int id;
    private int score;
    private Date date;

    public Scores() {
    }

    public Scores(int id, int score, Date date) {
        this.id = id;
        this.score = score;
        this.date = date;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    @Override
    public String toString() {
        return "Scores{" +
                "id=" + id +
                ", score=" + score +
                ", date=" + date +
                '}';
    }
}

3、创建一个通用的数据库操作类,也叫DAO类，用于数据表具体操作的父类

package com.ruqi.basedao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

/**
 * 封装一个对数据库表的通用操作
 * 以这个DAO为父类去创建相应的数据库表DAO实现类
 * 为了代码规范，在实现类之前先定义接口
 */
public abstract class BaseDAO {

    // 通用的增删改操作
    public void commonUpdate(Connection conn, String sql, Object... args){
        PreparedStatement ps = null;
        try{
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(null,ps,null);
        }
    }

    // 通用的查询操作
    public <T> List<T> commonSelect(Connection conn, Class<T> clazz, String sql, Object... args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[0]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmeta = rs.getMetaData();
            ArrayList<T> list = new ArrayList<T>();
            while (rs.next()){
                T t = clazz.newInstance();
                for (int i = 0; i <rsmeta.getColumnCount() ; i++) {
                    Object value = rs.getObject(i + 1);
                    String lable = rsmeta.getColumnLabel(i + 1);
                    Field file = clazz.getDeclaredField(lable);
                    file.setAccessible(true);
                    file.set(t, value);
                }
                list.add(t);
            }
            return list;

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(null,ps, rs);
        }
        return null;
    }

    // 查询返回只有一行一列的sql
    public <E> E getValue(Connection conn, String sql, Object... args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i +1 ,args[i]);
            }
            rs = ps.executeQuery();
            if(rs.next()){
                return (E) rs.getObject(1);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(null,ps,rs);
        }
        return null;
    }

    // 通用的查询操作，返回一条数据
    public <T> T selectOne(Connection conn, Class<T> clazz, String sql, Object... args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[0]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmeta = rs.getMetaData();
            if (rs.next()){
                T t = clazz.newInstance();
                for (int i = 0; i <rsmeta.getColumnCount() ; i++) {
                    Object value = rs.getObject(i + 1);
                    String lable = rsmeta.getColumnLabel(i + 1);
                    Field file = clazz.getDeclaredField(lable);
                    file.setAccessible(true);
                    file.set(t, value);
                }
                return t;
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(null,ps, rs);
        }
        return null;
    }
}

4、此时可以创建一个关于操作Scores的具体操作，但为了更加规范，可以先定义好接口再去生成实现类

package com.ruqi.basedao;

import java.sql.Connection;
import java.util.List;

public interface ScoresDAO {

    /**
     * 将对象数据插入到表中
     * @param conn
     * @param sc
     */
    void insert(Connection conn, Scores sc);

    /**
     * 通过id删除数据
     * @param conn
     * @param id
     */
    void deleteById(Connection conn, int id);

    /**
     * 更新成新的对象
     * @param conn
     */
    void update1(Connection conn, Scores sc);

    /**
     * 根据id查询对象
     * @param conn
     * @param id
     */
    Scores getScoresById(Connection conn, int id);

    /**
     * 查询所有数据
     * @param conn
     * @return
     */
    List<Scores> getAll(Connection conn);

    /**
     * 返回数据表的总数
     * @param conn
     * @return
     */
    Long getCount(Connection conn);

    /**
     * 返回最大的分数
     * @param conn
     * @return
     */
    int getMaxScore(Connection conn);
}

5、继承父类和接口，开始按接口的规范编写具体的操作

package com.ruqi.basedao;

import java.sql.Connection;
import java.util.List;

public class ScoresDAOImpl extends BaseDAO implements ScoresDAO{
    @Override
    public void insert(Connection conn, Scores sc) {
        String sql = "insert into Scores(score, date) values(?, ?);";
        commonUpdate(conn, sql, sc.getScore(), sc.getDate());
    }

    @Override
    public void deleteById(Connection conn, int id) {
        String sql = "delete from  Scores where id = ?;";
        commonUpdate(conn, sql, id);
    }

    @Override
    public void update1(Connection conn, Scores sc) {
        String sql = "update Scores set score = ?, date = ?  where id = ?;";
        commonUpdate(conn, sql, sc.getScore(), sc.getDate(), sc.getId());
    }

    @Override
    public Scores getScoresById(Connection conn, int id) {
        String sql = "select id, score,date from Scores where id = ?";
        Scores sc = selectOne(conn, Scores.class, sql, id);
        return sc;
    }

    @Override
    public List<Scores> getAll(Connection conn) {
        String sql = "select score,date from Scores";
        List<Scores> list = commonSelect(conn, Scores.class, sql);
        return list;
    }

    @Override
    public Long getCount(Connection conn) {
        return getValue(conn,"select count(1) from Scores");
    }

    @Override
    public int getMaxScore(Connection conn) {
        return getValue(conn, "select max(score) from Scores;");
    }
}

6、测试

package com.ruqi.basedao;

import org.junit.Test;

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

public class ScoresTest {

    private  ScoresDAOImpl simpl = new ScoresDAOImpl();

    @Test
    public void insert(){
        Connection conn = null;
        try{
            conn = ConnectUtil.getConnection();
            Scores sc = new Scores(1,1010, new Date(213131231223L));
            System.out.println(sc);
            simpl.insert(conn, sc);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(conn,null,null);
        }
    }


    @Test
    public void deleteById(){
        Connection conn = null;
        try{
            conn = ConnectUtil.getConnection();
            simpl.deleteById(conn, 1);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(conn,null,null);
        }
    }

    @Test
    public void update(){
        Connection conn = null;
        try{
            conn = ConnectUtil.getConnection();
            Scores sc = new Scores(2,110, new Date(16333333333L));
            simpl.update1(conn, sc);
            System.out.println(sc.getDate());
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(conn,null,null);
        }
    }

    @Test
    public void getAll(){
        Connection conn = null;
        try{
            conn = ConnectUtil.getConnection();
            List<Scores> list = simpl.getAll(conn);
            list.forEach(System.out::println);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(conn,null,null);
        }
    }

    @Test
    public void getcount(){
        Connection conn = null;
        try{
            conn = ConnectUtil.getConnection();
            long count = simpl.getCount(conn);
            System.out.println(count);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(conn,null,null);
        }
    }

    @Test
    public void getmax(){
        Connection conn = null;
        try{
            conn = ConnectUtil.getConnection();
            long MaxScore = simpl.getMaxScore(conn);
            System.out.println(MaxScore);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(conn,null,null);
        }
    }

    @Test
    public void getbyID(){
        Connection conn = null;
        try{
            conn = ConnectUtil.getConnection();
            Scores sc = simpl.getScoresById(conn, 3);
            System.out.println(sc);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(conn,null,null);
        }
    }
}

二、优化

针对上述第5步中的getScoresById，getAll中需要传Scores.class作为参数进行优化，由于我们已经知道具体的实现类就是操作Scores，可以进一步将该参数去除，并作相应的通用代码获取实现的类名

1、BaseDAO定义成泛型，实现类即可传入具体的类，详细看注释解释

package com.ruqi.basedao;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

public abstract class BaseDAO<T> {
    // 将BaseDAO定义成泛型，这样被继承时传入就可以拿到类名
    private Class<T> clazz = null;

    // 接着就是拿到BaseDAO<T>中的T具体是什么
    {
        {
            // this表示谁new一个对象，this就表示谁，BaseDAO作为父类，没有被new过，
            // 因此，在我们在new ScoresDAOImpl（子类）的时候，this仍然表示ScoresDAOImpl，所以以下代码仍然成立
            // 如果无法理解，可以将代码块放到ScoresDAOImpl便容易理解，但实际上应该写到父类，避免以后每个数据库表操作类都要写同样的代码
            Type genericSuperclass = this.getClass().getGenericSuperclass(); //获取带泛型的父类
            ParameterizedType paramType = (ParameterizedType) genericSuperclass; // 获取泛型的类型
            Type[] typeArguments = paramType.getActualTypeArguments(); //获取泛型的参数，泛型可能有多个，返回的是数组
            clazz = (Class<T>) typeArguments[0]; // 获取第一个值，即得到了Scores类名
        }
    }


    // 由于此时类已是泛型，已经拿到了类名，不再需要定义泛型方法，相当于T是已知的
//    public <T> List<T> commonSelect(Connection conn, Class<T> clazz, String sql, Object... args){
    public List<T> commonSelect(Connection conn, String sql, Object... args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[0]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmeta = rs.getMetaData();
            ArrayList<T> list = new ArrayList<T>();
            while (rs.next()){
                T t = clazz.newInstance();
                for (int i = 0; i <rsmeta.getColumnCount() ; i++) {
                    Object value = rs.getObject(i + 1);
                    String lable = rsmeta.getColumnLabel(i + 1);
                    Field file = clazz.getDeclaredField(lable);
                    file.setAccessible(true);
                    file.set(t, value);
                }
                list.add(t);
            }
            return list;

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(null,ps, rs);
        }
        return null;
    }

    // 由于此时类已是泛型，已经拿到了类名，不再需要定义泛型方法,相当于T是已知的
//    public <T> T selectOne(Connection conn, Class<T> clazz, String sql, Object... args){
    public T selectOne(Connection conn, String sql, Object... args){
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[0]);
            }
            rs = ps.executeQuery();
            ResultSetMetaData rsmeta = rs.getMetaData();
            if (rs.next()){
                T t = clazz.newInstance();
                for (int i = 0; i <rsmeta.getColumnCount() ; i++) {
                    Object value = rs.getObject(i + 1);
                    String lable = rsmeta.getColumnLabel(i + 1);
                    Field file = clazz.getDeclaredField(lable);
                    file.setAccessible(true);
                    file.set(t, value);
                }
                return t;
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            ConnectUtil.closeConnection(null,ps, rs);
        }
        return null;
    }
}

2、修改实现类不传类名，详细原因看注释

package com.ruqi.basedao;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.util.List;

// 继承父类时，就先将类名传入
//public class ScoresDAOImpl extends BaseDAO implements ScoresDAO{
public class ScoresDAOImpl extends BaseDAO<Scores> implements ScoresDAO{

    

    @Override
    public Scores getScoresById(Connection conn, int id) {
        String sql = "select id, score,date from Scores where id = ?";
        // 父类已经拿到了类名，不再需要传参
        // Scores sc = selectOne(conn, Scores.class, sql, id);
        Scores sc = selectOne(conn, sql, id);
        return sc;
    }

    @Override
    public List<Scores> getAll(Connection conn) {
        String sql = "select score,date from Scores";
        // 父类已经拿到了类名，不再需要传参
        // List<Scores> list = commonSelect(conn, Scores.class, sql);
        List<Scores> list = commonSelect(conn, sql);
        return list;
    }

}