简单的封装DAO----jdbc笔记

实现：
对象代表记录
成员变量代表字段
实现了一个通用的查询接口

首先遇到了一个问题：
这是收集到的解决方案 验证可以用；

jdbc连接mysql报错
在JDBC连接Mysql数据库的过程中出现了如下的警告信息:

WARN: Establishing SSL connection without server’s identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn’t set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to ‘false’. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

是Mysql数据库的SSL连接问题，提示警告不建议使用没有带服务器身份验证的SSL连接，是在MYSQL5.5.45+, 5.6.26+ and 5.7.6+版本中才有的这个问题。解决办法在警告中已经说明了：

1.在数据库连接的url中添加useSSL=false;
2.url中添加useSSL=true，并且提供服务器的验证证书。
如果只是做一个测试的话，没必要搞证书那么麻烦啦，在连接后添加一个useSSL=false即可，例如：

jdbc:mysql://localhost:3306/test?useSSL=false

接下来上正式代码：

---

目录结构和配置文件的内容

---

jdbc 数据操作类

package top.demo.test;

import java.awt.List;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;

import javax.print.attribute.standard.RequestingUserName;

import java.util.Properties;
import java.util.Set;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSetMetaData;


/*
 * 定义对数据库的通用的操作 
 * 
 * */
public class JdbcAbs {

    public Connection getConnection(){

        Connection con=null;
        try{
        Properties  properties=new Properties();
        properties.load(JdbcAbs.class.getResourceAsStream("/db.properties"));

        String name = properties.getProperty("name");
        String pass = properties.getProperty("pass");
        String dbtype = properties.getProperty("dbtype");
        String addr = properties.getProperty("addr");
        String port = properties.getProperty("port");
        String dbname = properties.getProperty("dbname");
        if(pass==null)
        {
            pass="";
        }

        StringBuilder dbUrl=new StringBuilder();
        //("jdbc:mysql://localhost:3306/javatest","root","");
        dbUrl.append("jdbc:").append(dbtype).append("://").append(addr).append(":").append(port).append("/")
        .append(dbname).append("?useSSL=false");

        //com.mysql.jdbc.Driver
        StringBuilder dbDriver=new StringBuilder().append("com.").append(dbtype).append(".jdbc.").append("Driver");

        //System.out.println(dbUrl.toString());

        Class.forName(dbDriver.toString() );

         con=(Connection)DriverManager.getConnection(dbUrl.toString(),name, pass);
        }catch (Exception e) {
            e.printStackTrace();
            return null;
        }

        return con;
    }


    public void add(Class cl,String sql,Object[] item){

        /*
         * 
         * 
         *
         *  insert into studentinfo (name,id_car as idcar,home_addr as home) values (1,2,3);
         * 
         * insert into studentinfo (name,id_car ,home_addr ) values ('1',123,'hz'),('你',55,'杭州');
         * */


    }



    public <T> ArrayList<T> getAll(Class<T> cl,String sql,Object ... PreparePrama) throws SQLException, InstantiationException, IllegalAccessException{

        /*
         * select  id_index id,name,id_car as idcar,home_addr as home from studentinfo;
         * 
         */

        Connection con =getConnection();

        PreparedStatement  pre=(PreparedStatement) con.prepareStatement(sql);

        if(PreparePrama!=null) {
            int parameterIndex=1;

            for(Object ob:PreparePrama) {
                pre.setObject(parameterIndex, ob);
                parameterIndex++;
            }
        }



        ResultSet resultSet= pre.executeQuery();
        ArrayList<T> list=new ArrayList<T>();

        while(resultSet.next()) {

            //获得员数据对象
            ResultSetMetaData metaData= (ResultSetMetaData) resultSet.getMetaData();

            Map<String,Object> ColunmMapping=new HashMap<String, Object> ();
            //获取一共有几列
            int count=metaData.getColumnCount();
            for(int i=1;i<=count;i++)
            {
                //获得列的别名
                String  columnlab=metaData.getColumnLabel(i);
                Object columnValue=resultSet.getObject(i);
                //把查询的每一条记录的 列名和值  放入map
                ColunmMapping.put(columnlab, columnValue);
            }

            //取出map的值  新建对象 根据属性放入值 然后 把对象放入 list
            T object=cl.newInstance();
            Set<Entry<String,Object>> entryset=ColunmMapping.entrySet();
            for(Entry<String,Object> entry:entryset) {
                Reflection.setAttr(cl, object,entry.getKey(), entry.getValue());
            }
            list.add(object);

        }

        con.close();
        pre.close();
        return  (ArrayList<T>) list;

    }



}

---

反射方法的工具

package top.demo.test;

import java.lang.reflect.Field;


/*
 * 定义反射接口
 * 通过传入class  、对象 、属性名 和 属性值 设置属性 
 * 
 * */
public class Reflection {


    public static boolean setAttr(Class cl,Object ob,String attrName,Object Value){


        Field attrField=null;

        try {

            attrField =cl.getDeclaredField(attrName);   
            attrField.setAccessible(true);
            attrField.set(ob, Value);

        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } 
        return  true;

    }




}

---

抽象表结构对象

package top.demo.test;



/*
 * 
 * Studentinfo 表的 抽象对象 
 * 
 * 
 * 
 * */

public class Student {



    /*
     * 
     * 设计表结构 
     * 
     * CREAETE database Student charset='utf8';
     * 
     * create table studentinfo(
     *   id_index int primary key auto_increment,
     *   name varchar(10),
     *   id_car int ,
     *   home_addr varchar(30)
     * )charset='utf8';
     * 
     * 
     * */

    private int id;
    private String name;
    private int idcar;
    private String home;



    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getIdcar() {
        return idcar;
    }
    public void setIdcar(int idcar) {
        this.idcar = idcar;
    }
    public String getHome() {
        return home;
    }
    public void setHome(String home) {
        this.home = home;
    }
    public Student() {
        super();
    }
    public Student(String name) {
        super();

    }




}

---

测试类

package top.demo.test;

import java.awt.List;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;




/*
 * 
 * 对 数据库的操作进行测试的类
 * 
 * */
public class Test {

    public static void main(String []argv) throws IOException, ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException{

        JdbcAbs db=new JdbcAbs();

        ArrayList<Student> list=db.getAll(Student.class, "select  id_index id,name,id_car as idcar,home_addr as home from studentinfo;", null);


        for(Student st:list) {
            System.out.println(st.getName()+"  "+st.getId()+" "+st.getHome());
        }

    }


}

输出结果 （结果是正确的，为了方便我随便插入的数据）

1 1 hz
1 2 hz
你 3 杭州