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();
} 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();
pre = con.prepareStatement(sql);
for (int i = 0; i < ages.length; i++) {
pre.setObject(i + 1, ages[i]);
}
re = pre.executeQuery();
ResultSetMetaData rsmd = re.getMetaData();
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);
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();
pre = con.prepareStatement(sql);
for (int i = 0; i < ages.length; i++) {
pre.setObject(i + 1, ages[i]);
}
re = pre.executeQuery();
ResultSetMetaData rsmd = re.getMetaData();
int columnCount = rsmd.getColumnCount();
ArrayList list = new ArrayList();
while (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);
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 {
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");
Class.forName(driverClass);
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