package com.augmentum.oes.common; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import com.augmentum.oes.exception.DBException; import com.augmentum.oes.util.DBUtil; public class JDBCTemplete<T> { public List<T> query(String sql, JDBCCallback <T> jdbccallbaclk) { Connection conn =null; PreparedStatement stmt = null; ResultSet rs = null; List<T> data = new ArrayList<T>(); boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql); jdbccallbaclk.setParams(stmt); rs = stmt.executeQuery(); while (rs.next()) { T object = jdbccallbaclk.rsToObject(rs); data.add(object); } } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, rs); if (needMyClose) { DBUtil.close(conn, null, null); } } return data; } public int insert(String sql, JDBCCallback<T> jdbcCallback) { Connection conn =null; PreparedStatement stmt = null; ResultSet rs = null; int id = 0; boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); jdbcCallback.setParams(stmt); stmt.executeUpdate(); rs = stmt.getGeneratedKeys(); if (rs.next()) { id = rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, null); if (needMyClose) { DBUtil.close(conn, null, null); } } return id; }; public void insertWithoutKey(String sql, JDBCCallback<T> jdbcCallback) { Connection conn =null; PreparedStatement stmt = null; boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql); jdbcCallback.setParams(stmt); stmt.executeUpdate(); } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, null); if (needMyClose) { DBUtil.close(conn, null, null); } } }; public T QueryOne(String sql, JDBCCallback<T> jdbcCallback) { List<T> data = query(sql, jdbcCallback); if (data !=null && !data.isEmpty()) { return data.get(0); } else { return null; } } public int update(String sql,JDBCCallback<T> jdbcCallback) { Connection conn =null; PreparedStatement stmt = null; int count = 0; boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql); jdbcCallback.setParams(stmt); count = stmt.executeUpdate(); } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, null); if (needMyClose) { DBUtil.close(conn, null, null); } } return count; } public int deleteByid(String sql, JDBCCallback<T> jdbcCallback) { Connection conn =null; PreparedStatement stmt = null; int count = 0; boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql); jdbcCallback.setParams(stmt); count = stmt.executeUpdate(); } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, null); if (needMyClose) { DBUtil.close(conn, null, null); } } return count; } public int getCount(String sql,JDBCCallback<T> jdbcCallback) { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; int count =0; boolean needMyClose = false; try { ConnectionHolder connectionHolder = (ConnectionHolder) AppContext.getAppContext().getObject("APP_REQUEST_THREAD_CONNECTION"); if (connectionHolder != null) { conn = connectionHolder.getConn(); } if (conn == null) { conn = DBUtil.getConnection(); needMyClose = true; } stmt = conn.prepareStatement(sql); jdbcCallback.setParams(stmt); rs = stmt.executeQuery(); if (rs.next()) { count = rs.getInt(1); } } catch (Exception e) { e.printStackTrace(); throw new DBException(); } finally { DBUtil.close(null, stmt, rs); if (needMyClose) { DBUtil.close(conn, null, null); } } return count; } public int getCountAll(String sql) { return this.getCount(sql, new JDBCAbstractCallback<T>() {}); } }
package com.augmentum.oes.common;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public interface JDBCCallback<T> {
T rsToObject(ResultSet rs) throws SQLException;
void setParams(PreparedStatement stmt) throws SQLException;
}
加入数据
package com.augmentum.oes.dao.impl; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.List; import org.springframework.jdbc.core.JdbcTemplate; import com.augmentum.oes.common.JDBCAbstractCallback; import com.augmentum.oes.common.JDBCCallback; import com.augmentum.oes.dao.QuestionDao; import com.augmentum.oes.model.Question; import com.augmentum.oes.util.Pagination; import com.augmentum.oes.util.StringUtil; public class QuestionDaoImpl implements QuestionDao{ private Question rsToQuestion(ResultSet rs) throws SQLException { Question question = new Question(); question.setId(rs.getInt("id")); question.setQuestion_desc(rs.getString("question_desc")); question.setRight_choice_name(rs.getString("right_choice_name")); question.setChoice_a(rs.getString("choice_a")); question.setChoice_b(rs.getString("choice_b")); question.setChoice_c(rs.getString("choice_c")); question.setChoice_d(rs.getString("choice_d")); question.setQuestion_status(rs.getInt("question_status")); return question; } private JdbcTemplate jdbcTemplate; @Override public Question queryById(final int question_id){ String sql = "SELECT * FROM question where id = ?" ; JDBCCallback<Question> j = new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setInt(1, question_id); super.setParams(stmt); } @Override public Question rsToObject(ResultSet rs) throws SQLException { return rsToQuestion(rs); } }; List<Question> list = jdbcTemplete.query(sql,j); return list.get(0); } @Override public int update(final Question question) { String sql = "UPDATE question SET question_desc=?,right_choice_name=?," + "choice_a=?, choice_b=?,choice_c=?,choice_d=?,question_status=? WHERE id = ? "; int count =jdbcTemplete.update(sql, new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setString(1, question.getQuestion_desc()); stmt.setString(2, question.getRight_choice_name()); stmt.setString(3, question.getChoice_a()); stmt.setString(4, question.getChoice_b()); stmt.setString(5, question.getChoice_c()); stmt.setString(6, question.getChoice_d()); stmt.setInt(7, question.getQuestion_status()); stmt.setInt(8, question.getId()); } }); return count; } @Override public List<Question> getListByKeyWord(final String keyword, Pagination pagination,String orderTags) { pagination.setTotalCount(this.getCount(keyword)); if (pagination.getCurrentPage() > pagination.getTotalCount()) { pagination.setCurrentPage(pagination.getTotalCount()); } String sql ="SELECT * FROM question WHERE question_status = 0 AND question_desc LIKE ? ORDER BY id "+(StringUtil.isEmpty(orderTags)?"ASC":"DESC")+" LIMIT "+pagination.getOffset()+","+pagination.getPageSize() ; List<Question> list = jdbcTemplete.query(sql, new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setString(1,"%"+keyword+"%"); } @Override public Question rsToObject(ResultSet rs) throws SQLException { return rsToQuestion(rs); } }); return list; } @Override public List<Question> getList(Pagination pagination,String orderTags) { pagination.setTotalCount(this.getCount(null)); if (pagination.getCurrentPage() > pagination.getTotalCount()) { pagination.setCurrentPage(pagination.getTotalCount()); } String sql ="SELECT * FROM question WHERE question_status = 0 ORDER BY id "+(StringUtil.isEmpty(orderTags)?"ASC":"DESC")+" LIMIT "+pagination.getOffset()+","+pagination.getPageSize() ; List<Question> list = jdbcTemplete.query(sql, new JDBCAbstractCallback<Question>() { @Override public Question rsToObject(ResultSet rs) throws SQLException { return rsToQuestion(rs); } }); return list; } @Override public int deleteById(final int id) { // String sql = "DELETE FROM question where id = ?"; String sql = "UPDATE question SET question_status=1 WHERE id= ?"; int count = jdbcTemplete.deleteByid(sql, new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setInt(1, id); } }); return count; } @Override public int getCount(final String question_desc) { int count =0; String sql = "SELECT count(*) FROM question WHERE question_status = 0"; if (StringUtil.isEmpty(question_desc)) { count = jdbcTemplete.getCountAll(sql); } else { sql = sql +" where question_desc LIKE ?"; count = jdbcTemplete.getCount(sql, new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setString(1,"%"+question_desc+"%"); super.setParams(stmt); } }); } return count; } @Override public int getNextId() { int nextId = 0; String sql = "SELECT max(id) FROM question"; nextId = jdbcTemplete.getCountAll(sql); return nextId; } @Override public void addUpdate(final Question question) { String sql = "INSERT INTO question(question_status) VALUES (?)"; jdbcTemplete.insert(sql, new JDBCAbstractCallback<Question>() { @Override public void setParams(PreparedStatement stmt) throws SQLException { stmt.setInt(1, question.getQuestion_status()); } }); } }
二 SpringJdbcTemplate
<context:property-placeholder location="classpath:config.properties"/> 也可以引入
引入数据库配置文件 <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"> <property name="locations"> <list> <value>classpath:config.properties</value> </list> </property> </bean>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close"> <property name="driverClass" value="${jdbc.driver}"></property> <property name="jdbcUrl" value="${jdbc.url}"></property> <property name="user" value="${jdbc.username}"></property> <property name="password" value="${jdbc.password}"></property> <property name="maxPoolSize" value="20" /> <property name="minPoolSize" value="5" /> <property name="acquireIncrement" value="3" /> <property name="initialPoolSize" value="5"></property> </bean>
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <!-- 初始化dataSource里面的参数 --> <property name="driverClassName" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql:///spring3_day2"></property> <property name="username" value="root"></property> <property name="password" value="root"></property> </bean>
applicationContext注入
<bean id="baseDao" abstract="true"> <property name="jdbcTemplate" ref="jdbcTemplate"/> </bean> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" > <property name="dataSource" ref="dataSource"/> </bean> <bean class="com.augmentum.oes.util.SpringUtil" /> 其他dao 配置parent=“basedao”
web.xml监听注入
<context-param> <param-name>contextConfigLocation</param-name> <param-value>classpath:applicationContext.xml</param-value> </context-param> <listener> <display-name>contextLoaderListener</display-name> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener>
读取applicationContext.xml 来得到相应实例的类 需先在xml中写入 <bean class="com.augmentum.oes.util.SpringUtil" />
web.xml加载会通过ApplicationContextAware接口找到他
public class SpringUtil implements ApplicationContextAware{
private static ApplicationContext applicationContext = null;
@Override
/**
* nedd set in applicationContext.xml , read the ApplicationContextAware interface
*/
public void setApplicationContext(ApplicationContext ac) throws BeansException { //beanFactory
applicationContext = ac;
}
public static ApplicationContext getApplicationContext() {
return applicationContext;
}
public static Object getBean(String beanId) {
ApplicationContext applicationContext = getApplicationContext();
return applicationContext.getBean(beanId);
}
}