JAVA JBDC连接MySql数据库示例心得一

①–>下载MySql数据库驱动解压获得JAR文件导入编写的Java程序中
下图中1是复制过来的驱动文件，2是导入的文件，要导入才可以用。

②–> 连接数据库，数据查询，数据更新

A是数据库对应的数据类如下：

package com.jdbc;

public class A {

    private int id;
    private String name;
    private String password;
    private String email;

    public A() {

    }

    public A(int id, String name, String password, String email) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.email = email;
    }

    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 String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "A [id=" + id + ", name=" + name + ", password=" + password + ", email=" + email + "]";
    }

}

jdbc.properties是数据库配置文件：

driver=com.mysql.jdbc.Driver
jdbcurl=jdbc:mysql://localhost:3306/demodb?useUnicode=true&characterEncoding=utf-8&useSSL=false
user=root
password=123456

JDBCTools是JDBC工具类，包括数据库连接，数据更新如下：

package com.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
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.util.HashMap;
import java.util.Map;
import java.util.Properties;

import com.mysql.jdbc.Statement;

public class JDBCTools {
    /**
     * 获取数据库连接
     * */
    public Connection GetConnection() throws IOException, SQLException, ClassNotFoundException {
        //使用配置文件方式连接数据库
        Properties properties = new Properties();
        //获取流
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("jdbc.properties");
        properties.load(in);
        String driver = properties.getProperty("driver");
        String jdbcurl = properties.getProperty("jdbcurl");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        Class.forName(driver);
        return DriverManager.getConnection(jdbcurl, user, password);
    }
    /**
     * 数据获取
     * */
    public <T> T get(Class<T> clazz,String sql,Object ...obj){
        T eneity = null;
        Connection conn = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ResultSetMetaData setMetaData = null;
        try {
            conn = GetConnection();
            preparedStatement = conn.prepareStatement(sql);
            for(int i=0;i<obj.length;i++){
                preparedStatement.setObject(i+1, obj[i]);
            }
            resultSet = preparedStatement.executeQuery();//得到结果集
            //得到ResultSetMetaData对象
            setMetaData = resultSet.getMetaData();
            //创建一个Map<String ,Object>对象，键：SQL查询的列的别名，值：列的值
            Map<String, Object> values = new HashMap<String, Object>();
            //处理结果集，利用ResultSetMetaData填充对应的Map对象
            if(resultSet.next()){
                for(int i=0;i<setMetaData.getColumnCount();i++){
                    String columnLabel = setMetaData.getColumnLabel(i+1);//得到列名
                    Object columnValue = resultSet.getObject(i+1);
                    System.out.println(columnValue.getClass().getName());
                    //System.out.println(columnLabel+":"+columnValue);
                    //键列名与对应的值存入Map对象
                    values.put(columnLabel, columnValue);
                }
                if(values.size()>0){
                    //若Map不为空集，利用反射创建clazz对应的对象
                    eneity = clazz.newInstance();
                    //遍历Map对象，取出对应的属性的值
                    for(Map.Entry<String, Object> entry:values.entrySet()){
                        String fieldName = entry.getKey();
                        Object value = entry.getValue();
                        //利用反射保存数据
                        SetValues(eneity, fieldName, value);
                    }
                }
            }
            return eneity;

        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            //关闭连接
            release(resultSet, (Statement) preparedStatement, conn);
        }
        return eneity;
    }
    /**
     * 保存数据库数据到对应的JAVA类中
     * */
    public <T> void SetValues(T t,String fieldName,Object value){
        //将传过来的值首字母大写
        String methodName = fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1); 
        Method method = null; 
        try {
            //获取该类下的所有属性值
            Field field = t.getClass().getDeclaredField(fieldName);
            //初始化变量类型数组
            Class[] parameterTypes = new Class[1];
            //获取该属性值的变量类型对象
            parameterTypes[0] = field.getType();  
            //传入方法名称及方法的类型获取该方法的对象
            method = t.getClass().getMethod("set"+methodName,parameterTypes);
            //获取传人的值的变量类型
            method.invoke(t,value);
         } catch (Exception e) {
           e.printStackTrace();
         }
    }

    /**
     * 执行SQL的方法,包含insert,delete,updata
     * sql="insert into A (id,name,password,email) values(?,?,?,?);";
     * ?号代表条件值
     * */
    public void Updata(String sql,Object ...obj){
        Connection conn = null;
        PreparedStatement preparedStatement= null;
        try {
            conn = GetConnection();
            preparedStatement = conn.prepareStatement(sql);
            for(int i=0;i<obj.length;i++){
                preparedStatement.setObject(i+1, obj[i]);
            }
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            release(null, (Statement) preparedStatement, conn);
        }
    }
    /**
     * 关闭数据库
     * */
    public static void release(ResultSet rs, Statement sm, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (sm != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试代码：

JDBCTools jdbcTools = new JDBCTools();
        A a = jdbcTools.get(A.class, sql6, 3);
        System.out.println(a);