自己写的一个java链接数据库的类

转载

mb5fed6f751964c 2020-04-23 14:54:00

文章标签 jdbc数据库连接 sql java i++ 数据库 文章分类 代码人生

和类同路径的数据库配置文件: db.properties

jdbc.driver = com.mysql.jdbc.Driver
jdbc.host = jdbc:mysql://localhost:3306/test
jdbc.username = root
jdbc.password = 89757
jdbc.port = 3306

类库文件: JdbcDao.java

package com.cc8w.dao;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;

/**
 *  数据库操作类
 * @author Administrator
 *
 */
public class JdbcDao {
    private Connection conn = null;
    private Statement st = null;//Statement
    private PreparedStatement ps = null;//PreparedStatement
    private ResultSet rs = null;

    //构造方法连接数据库信息
    public JdbcDao() {
        Properties properties = new Properties();
        InputStream is=null;
        try {
            //is = new FileInputStream("/db.properties");
            is = this.getClass().getResourceAsStream("/db.properties");
            properties.load(is);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            System.exit(0);
        }

        String driver = properties.getProperty("jdbc.driver");
        String host = properties.getProperty("jdbc.host");
        String username = properties.getProperty("jdbc.username");
        String password = properties.getProperty("jdbc.password");
        String port = properties.getProperty("jdbc.port");
        try {
            Class.forName(driver);
            this.conn = DriverManager.getConnection(host, username, password);
            this.st = conn.createStatement();

        } catch (ClassNotFoundException | SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


    }

    //新增操作(返回真假)
    public boolean add(String sql,Object ... args)
    {
        int flag = 0;
        try {
            ps = conn.prepareStatement(sql);
            if(ps==null) System.exit(0);
            int i=1;
            for(Object o :args) {
                ps.setObject(i, o);
                i++;
            }
            //res = ps.execute();  //为什么不用即使成功了也返回false
            flag = ps.executeUpdate();//成功返回1,失败返回0

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


        if(flag==0) {
            return false;
        }else {
            return true;
        }


    }

    //新增操作(返回新增行id)
    public int addInsertId(String sql ,Object ... args) {
        int flag = 0;
        try {
            ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
            if(ps==null) System.exit(0);
            int i=1;
            for(Object o :args) {
                ps.setObject(i, o);
                i++;
            }
            flag = ps.executeUpdate();//成功返回1,失败返回0
            if(flag!=0) {
                ResultSet rs = ps.getGeneratedKeys();
                if(rs.next()){ 
                    return rs.getInt(1); 
                } 
            }


        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return 0;
    }




    //更新操作
    public boolean update(String sql,Object ... args) {
        int flag = 0;
        try {
            ps = conn.prepareStatement(sql);
            if(ps==null) System.exit(0);
            int i=1;
            for(Object o :args) {
                ps.setObject(i, o);
                i++;
            }
            flag = ps.executeUpdate();//成功返回1,失败返回0

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


        if(flag==0) {
            return false;
        }else {
            return true;
        }
    }

    //删除操作(同上)
    public boolean del(String sql,Object ... args) {
        int flag = 0;
        try {
            ps = conn.prepareStatement(sql);
            if(ps==null) System.exit(0);
            int i=1;
            for(Object o :args) {
                ps.setObject(i, o);
                i++;
            }
            flag = ps.executeUpdate();//成功返回1,失败返回0

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


        if(flag==0) {
            return false;
        }else {
            return true;
        }

    }

    //查询一条记录
    public <T> T getOne(Class<T> clazz,String sql,Object ... args) {
        T t=null;
        try {
            t = clazz.newInstance();
        } catch (InstantiationException | IllegalAccessException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }

        try {
            //1.ps里面->查询出resultSet

            ps = conn.prepareStatement(sql);
            if(ps==null) System.exit(0);
            int i=1;
            for(Object o :args) {
                ps.setObject(i, o);
                i++;
            }
            //2.ps里面->ResultSetMetaData
            rs = ps.executeQuery();//获取数据表头
            ResultSetMetaData rsmd = ps.getMetaData();
            int colNum = rsmd.getColumnCount();
            String []colLable = new String[colNum] ;
            for(i=1;i<=(colNum);i++) {
                colLable[i-1] = rsmd.getColumnLabel(i);
            }                    

            while(rs.next()){

                for(String dd :colLable) {
                    Field f = clazz.getDeclaredField(dd);
                    f.setAccessible(true);
                    f.set(t, rs.getObject(dd));
                }

                continue; //跳出本次循环,只取一条记录

            }


        } catch (SQLException | NoSuchFieldException | SecurityException | IllegalArgumentException | IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return t;
    }


    //查询多条记录
    public <T> ArrayList<T>  getAll(Class<T> clazz,String sql,Object ... args) {
        //ArrayList<String> ss = new ArrayList<String>();
        ArrayList<T> arrt = new ArrayList<T>();
        T t=null;


        try {
            //1.ps里面->查询出resultSet
            ps = conn.prepareStatement(sql);
            if(ps==null) System.exit(0);
            int i=1;
            for(Object o :args) {
                ps.setObject(i, o);
                i++;
            }
            //2.ps里面->ResultSetMetaData
            rs = ps.executeQuery();//获取数据表头
            ResultSetMetaData rsmd = ps.getMetaData();
            int colNum = rsmd.getColumnCount();
            String []colLable = new String[colNum] ;
            for(i=1;i<=(colNum);i++) {
                colLable[i-1] = rsmd.getColumnLabel(i);
            }                

            while(rs.next()){
                t = clazz.newInstance();
                for(String dd :colLable) {
                    Field f = clazz.getDeclaredField(dd);
                    f.setAccessible(true);

                    f.set(t, rs.getObject(dd));
                }

                arrt.add(t);

            }


        } catch (SQLException | NoSuchFieldException | SecurityException | IllegalArgumentException | IllegalAccessException | InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


        return arrt;
    }


    //返回任意值类型
    @SuppressWarnings("unchecked")
    public <V> V get(Object obj){
        return (V)obj;
    }

    //数据库关闭
    public void close() {

        try {
            if(rs!=null) rs.close();
            if(ps!=null) ps.close();
            if(st!=null) st.close();
            if(conn!=null) conn.close();    
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    //析构函数
    public void finalize()
    {
        close();
    }


}

===================================下面试下===

测试实体: Teacher.java

package com.cc8w.entity;

import java.util.Date;

public class Teacher {
    private int id=0;
    private String name ="";
    private int no =0;
    private Date create_time = null;
    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 getNo() {
        return no;
    }
    public void setNo(int no) {
        this.no = no;
    }
    public Date getCreate_time() {
        return create_time;
    }
    public void setCreate_time(Date create_time) {
        this.create_time = create_time;
    }
    @Override
    public String toString() {
        return "Teacher [id=" + id + ", name=" + name + ", no=" + no + ", create_time=" + create_time + "]";
    }




}

测试:Test.java

package com.cc8w.test;

import java.util.ArrayList;
import com.cc8w.dao.JdbcDao;
import com.cc8w.entity.Teacher;


public class Test {

    public static void main(String[] args) {
        // TODO Auto-generated method stub

        JdbcDao dd = new JdbcDao();
        System.out.println(dd);
//        String sql = "insert into teacher (`name`,`no`,`create_time`) values (?,?,?)";
//        Boolean res = dd.add(sql,"1234",1111,"2020-04-04");
//        System.out.println(res);

        String sql = "select * from teacher";
        ArrayList<Teacher> t = dd.getAll(Teacher.class,sql);
        System.out.println(t);

        Teacher tt = dd.getOne(Teacher.class,sql);
        System.out.println(tt);



    }

}