JAvaのDBHelper
7917 ワード
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.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
public class DBHelper{
//
private Connection conn = null;
// PreparedStatement
private PreparedStatement psts = null;
// ResultSet
private ResultSet rs = null;
//
private static String className = "com.mysql.jdbc.Driver";
// URL
private static String url = "jdbc:mysql://localhost:3306/myweb";
//
private static String user = "root";
//
private static String pwd = "root";
// ThreadLocal
private static ThreadLocal threadlocal = new ThreadLocal<>();
static {
try {
InputStream in = DBHelper.class.getResourceAsStream("/dbconfig.properties");
Properties p = new Properties();
p.load(in);
//
className = p.getProperty("jdbc.className");
// URL
url = p.getProperty("jdbc.url");
//
user = p.getProperty("jdbc.user");
//
pwd = p.getProperty("jdbc.password");
} catch (IOException e) {
e.printStackTrace();
System.out.println(" 。");
}
}
// 2.
static {
try {
//
Class.forName(className);
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println(" !");
}
}
// 3.
/**
*
*/
private void getConnection() throws SQLException {
//
conn = threadlocal.get();
if (conn == null || conn.isClosed()) {
conn = DriverManager.getConnection(url, user, pwd);
threadlocal.set(conn);
}
}
/**
*
*
* @param sql
* SQL
* @param objs
* SQL ,
* @return
*/
public long executeInsert(String sql, Object... objs) throws SQLException {
//
getConnection();
// PreparedStatement
psts = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
// , ?
setParameters(objs);
// SQL
psts.executeUpdate();
//
rs = psts.getGeneratedKeys();
//
if (rs.next())
return rs.getLong(1);
else
return -1;
}
/**
* ( 、 、 )
*
* @param sql
* SQL
* @param objs
* SQL ,
* @return
* @throws SQLException
*
*/
public int executeUpdate(String sql, Object... objs) throws SQLException {
//
getConnection();
// PreparedStatement
psts = conn.prepareStatement(sql);
// , ?
setParameters(objs);
// SQL
return psts.executeUpdate();
}
/**
*
*
* @param sql
* SQL
* @param objs
* SQL ,
* @return
* @throws SQLException
*/
public ResultSet executeQuery(String sql, Object... objs) throws SQLException {
//
getConnection();
// PreparedStatement
psts = conn.prepareStatement(sql);
// , ?
setParameters(objs);
// SQL
return psts.executeQuery();
}
/**
*
*
* @param sql
* SQL
* @param objs
* SQL ,
* @return
* @throws SQLException
*/
public Object executeScalar(String sql, Object... objs) throws SQLException {
//
getConnection();
// PreparedStatement
psts = conn.prepareStatement(sql);
// , ?
setParameters(objs);
// SQL
rs = psts.executeQuery();
//
if (rs.next())
return rs.getObject(1);
else
return null;
}
/**
* (IRowMapper : )
* @param rowmapper
* @param sql
* @param objs
* @return
* @throws SQLException
*/
public List getList(IRowMapper rowmapper,String sql, Object... objs) throws SQLException{
//
rs = executeQuery(sql, objs);
//
List list = new ArrayList<>();
//
while(rs.next()) {
T t= rowmapper.rowMapper(rs);
list.add(t);
}
return list;
}
/**
* ( )
* @param tclass
* @param sql
* @param objs
* @return
* @throws SQLException
* @throws SecurityException
* @throws NoSuchFieldException
* @throws IllegalAccessException
* @throws InstantiationException
*/
public List getList(Class tclass, String sql, Object... objs) throws SQLException, NoSuchFieldException, SecurityException, InstantiationException, IllegalAccessException{
//
rs = executeQuery(sql, objs);
//
List list = new ArrayList();
// ( )
// , . 。 。
Field[] fs = tclass.getDeclaredFields();
//
if (rs != null) {
while (rs.next()) {
//
T t = (T) tclass.newInstance();
//
for (int i = 0; i < fs.length; i++) {
/*
* fs[i].getName():
*
* f:
*/
Field f = t.getClass().getDeclaredField(fs[i].getName());
// true
f.setAccessible(true);
/*
* f.getType().getName():
*/
//
if (f.getType().getName().equals(String.class.getName())) {
f.set(t, rs.getString(fs[i].getName()));
} else if (f.getType().getName().equals(int.class.getName())) {
f.set(t, rs.getInt(fs[i].getName()));
}
else if (f.getType().getName().equals(short.class.getName())) {
f.set(t, rs.getShort(fs[i].getName()));
}
else if (f.getType().getName().equals(byte.class.getName())) {
f.set(t, rs.getByte(fs[i].getName()));
}
else if (f.getType().getName().equals(long.class.getName())) {
f.set(t, rs.getLong(fs[i].getName()));
}
else if (f.getType().getName().equals(double.class.getName())) {
f.set(t, rs.getDouble(fs[i].getName()));
}
else if (f.getType().getName().equals(float.class.getName())) {
f.set(t, rs.getFloat(fs[i].getName()));
}
else if (f.getType().getName().equals(Date.class.getName())) {
f.set(t, rs.getDate(fs[i].getName()));
}
else if (f.getType().getName().equals(boolean.class.getName())) {
f.set(t, rs.getBoolean(fs[i].getName()));
}
}
list.add(t);
}
}
//
return list;
}
/**
* SQL
*
* @param objs
* @throws SQLException
*/
private void setParameters(Object... objs) throws SQLException {
if (objs != null)
for (int i = 0; i < objs.length; i++) {
psts.setObject(i + 1, objs[i]);
}
}
/**
*
* @throws SQLException
*/
public void beginTransaction() throws SQLException {
//
getConnection();
conn.setAutoCommit(false);
}
/**
*
* @throws SQLException
*/
public void commitTransaction() throws SQLException {
conn.commit();
}
/**
*
* @throws SQLException
*/
public void rollbackTransaction() throws SQLException {
conn.rollback();
}
// 8.
/**
*
*/
public void close() {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
rs = null;
e.printStackTrace();
}
try {
if (psts != null)
psts.close();
} catch (SQLException e) {
psts = null;
e.printStackTrace();
}
if (conn != null)
conn = null;
}
}
interface IRowMapper {
public T rowMapper(ResultSet rs) throws SQLException;
}