JDBC常用模板

JDBC常用模板

增删改模板

public static int updata(String sql, Object... arge) {
        Connection con = null;
        PreparedStatement pre = null;
        try {
            con = JDBCUtil.GetUtils();
            pre = con.prepareStatement(sql);
            for (int i = 0; i < arge.length; i++) {
                pre.setObject(i + 1, arge[i]);
            }
            return pre.executeUpdate();
            /*
             *  pre.execute();
             * 执行查询，有返回结果 ，是true
             * 增删改 没有返回结果 ，默认false
             * */
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(con, pre);
        }
        return 0;
    }

查询模板-不同表单

 public static <T> T getInstance(Class<T> clazz, String sql, Object... ages) {
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        try {
            //打开连接
            con = JDBCUtil.GetUtils();

            //编译sql 返回 prepareStatement对象
            pre = con.prepareStatement(sql);
            //设置sql语句占位符的值
            for (int i = 0; i < ages.length; i++) {
                pre.setObject(i + 1, ages[i]);
            }
//2执行  并返回结果集
            re = pre.executeQuery();
            //获取元数据 ResultSetMetaData
            ResultSetMetaData rsmd = re.getMetaData();

            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            if (re.next()) {
                T t = clazz.newInstance();
                //处理结果集数据中的每一列
                for (int i = 0; i < columnCount; i++) {
                    //拿到每个列的值
                    Object columnValue = re.getObject(i + 1);
                    //获取列名
                    String columnName = rsmd.getColumnLabel(i + 1);
                    //给cust对象指定columnName属性。赋值为columValue:通过反射
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(con, pre, re);
        }

        return null;

    }

结果集查询

 public <T> List<T> getForlist(Class<T> clazz, String sql, Object... ages) {

        Connection con = null;
        PreparedStatement pre = null;
        ResultSet re = null;
        try {
            //打开连接
            con = JDBCUtil.GetUtils();

            //编译sql 返回 prepareStatement对象
            pre = con.prepareStatement(sql);
            //设置sql语句占位符的值
            for (int i = 0; i < ages.length; i++) {
                pre.setObject(i + 1, ages[i]);
            }
//2执行  并返回结果集
            re = pre.executeQuery();
            //获取元数据 ResultSetMetaData
            ResultSetMetaData rsmd = re.getMetaData();

            //通过ResultSetMetaData获取结果集中的列数
            int columnCount = rsmd.getColumnCount();
            //创建集合对象
            ArrayList list = new ArrayList();
            while (re.next()) {
                T t = clazz.newInstance();
                //处理结果集数据中的每一列
                //给t对象指定属性赋值
                for (int i = 0; i < columnCount; i++) {
                    //拿到每个列的值
                    Object columnValue = re.getObject(i + 1);
                    //获取列名
                    String columnName = rsmd.getColumnLabel(i + 1);
                    //给cust对象指定columnName属性。赋值为columValue:通过反射
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(con, pre, re);
        }

        return null;

    }

JDBCUtil模板

package com.yyk2.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    //获取连接数据
    public static Connection getConnection() throws Exception {
        //   1读取加载文件的配置信息
//        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("drivername");
//         2加载驱动
        Class.forName(driverClass);
//        3 获取连接
        Connection connection = DriverManager.getConnection(url, user, password);
        return connection;
    }

    public static void closeResource(Connection connection, Statement preparedStatement) {
        //          关闭资源
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        try {
            if (preparedStatement != null)
                preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
    }

    public static void closeResource(Connection connection, Statement preparedStatement, ResultSet resultSet) {
        //          关闭资源
        try {
            if (connection != null)
                connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        try {
            if (preparedStatement != null)
                preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
        try {
            if (resultSet != null)
                resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        }
    }
}

配置文件 jdbc.properties

url =jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
user = root
password = root
drivername = com.mysql.jdbc.Driver