public <T> List<T> getListBySQL(String sql, Object[] values, RowMapper<T> rm) {
this.log("query sql [ " + sql + " ]");
PreparedStatement ps = null;
ResultSet rs = null;
Connection con = this.getCurrentConnection();
try {
ps = con.prepareStatement(sql);
if (values != null) {
for (int i = 0; i < values.length; i++) {
ps.setObject(i + 1, values[i]);
}
}
rs = ps.executeQuery();
int rowNum = 0;
List<T> list = new ArrayList<T>();
while (rs.next()) {
list.add(rm.mapRow(rs, rowNum++));
}
return list;
} catch (SQLException e) {
JdbcUtils.closeStatement(ps);
ps = null;
DataSourceUtils.releaseConnection(con, getDataSource());
con = null;
e.printStackTrace();
throw getExceptionTranslator().translate("StatementCallback", sql, e);
} finally {
JdbcUtils.closeResultSet(rs);
JdbcUtils.closeStatement(ps);
DataSourceUtils.releaseConnection(con, getDataSource());
}
}
public Object insertBySQL(String sql, Object[] values) {
this.log("insert sql [ " + sql + " ]");
PreparedStatement ps = null;
ResultSet rs = null;
Connection con = this.getCurrentConnection();
try {
ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
if (values != null) {
int i = 1;
for (Object value : values) {
ps.setObject(i++, value);
}
}
ps.executeUpdate();
rs = ps.getGeneratedKeys();
if (rs.next()) {
return rs.getObject(1);
}
return 0;
} catch (SQLException e) {
JdbcUtils.closeStatement(ps);
ps = null;
DataSourceUtils.releaseConnection(con, getDataSource());
con = null;
e.printStackTrace();
throw getExceptionTranslator().translate("StatementCallback", sql, e);
} finally {
JdbcUtils.closeResultSet(rs);
JdbcUtils.closeStatement(ps);
DataSourceUtils.releaseConnection(con, getDataSource());
}
}
public int updateBySQL(String sql, Object[] values) {
this.log("update sql [ " + sql + " ]");
PreparedStatement ps = null;
ResultSet rs = null;
Connection con = this.getCurrentConnection();
try {
ps = con.prepareStatement(sql);
if (values != null) {
for (int i = 0; i < values.length; i++) {
ps.setObject(i + 1, values[i]);
}
}
int rows = ps.executeUpdate();
return rows;
} catch (SQLException e) {
JdbcUtils.closeStatement(ps);
ps = null;
DataSourceUtils.releaseConnection(con, getDataSource());
con = null;
e.printStackTrace();
throw getExceptionTranslator().translate("StatementCallback", sql, e);
} finally {
JdbcUtils.closeResultSet(rs);
JdbcUtils.closeStatement(ps);
DataSourceUtils.releaseConnection(con, getDataSource());
}
}
/**
* Extract database meta data via the given DatabaseMetaDataCallback.
*
* <p>This method will open a connection to the database and retrieve the database metadata. Since
* this method is called before the exception translation feature is configured for a datasource,
* this method can not rely on the SQLException translation functionality.
*
* <p>Any exceptions will be wrapped in a MetaDataAccessException. This is a checked exception and
* any calling code should catch and handle this exception. You can just log the error and hope
* for the best, but there is probably a more serious error that will reappear when you try to
* access the database again.
*
* @param dataSource the DataSource to extract metadata for
* @param action callback that will do the actual work
* @return object containing the extracted information, as returned by the
* DatabaseMetaDataCallback's {@code processMetaData} method
* @throws MetaDataAccessException if meta data access failed
*/
public static Object extractDatabaseMetaData(
DataSource dataSource, DatabaseMetaDataCallback action) throws MetaDataAccessException {
Connection con = null;
try {
con = DataSourceUtils.getConnection(dataSource);
if (con == null) {
// should only happen in test environments
throw new MetaDataAccessException(
"Connection returned by DataSource [" + dataSource + "] was null");
}
DatabaseMetaData metaData = con.getMetaData();
if (metaData == null) {
// should only happen in test environments
throw new MetaDataAccessException(
"DatabaseMetaData returned by Connection [" + con + "] was null");
}
return action.processMetaData(metaData);
} catch (CannotGetJdbcConnectionException ex) {
throw new MetaDataAccessException("Could not get Connection for extracting meta data", ex);
} catch (SQLException ex) {
throw new MetaDataAccessException("Error while extracting DatabaseMetaData", ex);
} catch (AbstractMethodError err) {
throw new MetaDataAccessException(
"JDBC DatabaseMetaData method not implemented by JDBC driver - upgrade your driver", err);
} finally {
DataSourceUtils.releaseConnection(con, dataSource);
}
}
public void excuteBatch() throws BusinessException {
Connection conn = null;
PreparedStatement psmt = null;
synchronized (vector) {
try {
conn = DataSourceUtils.getConnection(SessionFactoryUtils.getDataSource(sessionFactory));
conn.setAutoCommit(false);
psmt = conn.prepareStatement(SqlFactory.getInstance().insertPolicyDevice());
for (PolicyDevice pd : vector) {
psmt.setString(1, (String) new UUIDHexGenerator().generate(null, null));
psmt.setString(2, pd.getPolicyId());
psmt.setString(3, pd.getDeviceId());
psmt.setShort(4, pd.getType());
psmt.setShort(5, pd.getStatus());
psmt.addBatch();
}
psmt.executeBatch();
conn.commit();
vector.clear();
} catch (SQLException e) {
e.printStackTrace();
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
throw new BusinessException(
ErrorCode.DATABASE_ACCESS_ERROR, "Could not get database connnection");
} catch (Exception e) {
e.printStackTrace();
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException e1) {
e1.printStackTrace();
}
throw new BusinessException(ErrorCode.ERROR, e.getMessage());
} finally {
try {
if (psmt != null) {
psmt.close();
psmt = null;
}
if (conn != null) {
DataSourceUtils.releaseConnection(
conn, SessionFactoryUtils.getDataSource(sessionFactory));
}
} catch (Exception e) {
e.printStackTrace();
throw new BusinessException(
ErrorCode.DATABASE_ACCESS_ERROR, "Could not close database connnection");
}
}
}
}
public void fill() throws Exception {
Connection connection = null;
try {
connection = DataSourceUtils.getConnection(dataSource);
IDatabaseConnection dbUnitConnection = new DatabaseConnection(connection);
FullXmlDataFileLoader loader = new FullXmlDataFileLoader();
IDataSet xmlDataSet = loader.load(xmlFilename);
DatabaseOperation.CLEAN_INSERT.execute(dbUnitConnection, xmlDataSet);
} finally {
DataSourceUtils.releaseConnection(connection, dataSource);
}
}
/*
* Return a computer using its id
*/
public Computer get(Long computerId) throws SQLException, ParseException {
logger.debug("Enterring get(Long computerId) in ComputerDAO.");
Connection connection = DataSourceUtils.getConnection(datasource);
String query =
"SELECT * FROM `computer-database-db`.`computer` AS computer LEFT OUTER JOIN `computer-database-db`.`company` AS company ON computer.company_id=company.id WHERE computer.id=?;";
PreparedStatement statement = connection.prepareStatement(query);
statement.setLong(1, computerId);
ResultSet resultSet = statement.executeQuery();
Computer computer = null;
while (resultSet.next()) {
Company company =
Company.builder().id(resultSet.getLong(5)).name(resultSet.getString(7)).build();
computer =
Computer.builder()
.id(resultSet.getLong(1))
.name(resultSet.getString(2))
.company(company)
.build();
if (resultSet.getDate(3) != null) {
computer.setIntroduced(new DateTime(resultSet.getDate(3)));
}
if (resultSet.getDate(4) != null) {
computer.setDiscontinued(new DateTime(resultSet.getDate(4)));
}
}
if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
logger.debug("Leaving get(Long computerId) in ComputerDAO.");
return computer;
}
@Override
public List<BlockStatus> findAllByBlockStatusExample(
BlockStatusExample blockStatusExample, int offset, int limit) throws DaoException {
try {
Connection conn = DataSourceUtils.doGetConnection(dataSource);
Statement stmt = conn.createStatement();
String clause =
BlockStatusDaoImpl.buildClause(blockStatusExample) + " limit " + offset + "," + limit;
logger.debug(
"DB:FIND block_status[distinct="
+ blockStatusExample.isDistinct()
+ ",clause="
+ clause
+ "]");
ResultSet rs =
stmt.executeQuery(
"select"
+ (blockStatusExample.isDistinct() ? " distinct " : " ")
+ "`id`,`id2`,`shipment_id`,`declaration_no`,`status`,`remark`,`block_date_time`,`unblock_date_time`,`company_code`,`company_type`,`user_block`,`mawb`,`hawb`,`flight_no`,`flight_date`,`user_unblock`,`modified_date_time`,`auto_block_profile_id` from block_status"
+ clause);
List<BlockStatus> list = new ArrayList<BlockStatus>();
while (rs.next()) {
list.add(BlockStatusDaoImpl.createInstanceFromResultSet(rs));
}
rs.close();
stmt.close();
return list;
} catch (Exception e) {
throw new DaoException(e);
}
}
@Override
public int countByBlockStatusExample(BlockStatusExample blockStatusExample) throws DaoException {
try {
Connection conn = DataSourceUtils.doGetConnection(dataSource);
Statement stmt = conn.createStatement();
String clause = BlockStatusDaoImpl.buildClause(blockStatusExample);
ResultSet rs =
stmt.executeQuery(
"select"
+ (blockStatusExample.isDistinct() ? " distinct " : " ")
+ "count(*) from block_status"
+ clause);
int count = -1;
if (rs.next()) {
count = rs.getInt(1);
}
rs.close();
stmt.close();
logger.debug(
"DB:COUNT block_status[distinct="
+ blockStatusExample.isDistinct()
+ ",clause="
+ clause
+ "] => "
+ count);
return count;
} catch (Exception e) {
throw new DaoException(e);
}
}
@Override
public int insert(BlockStatus blockStatus) throws DaoException {
if (blockStatus == null) {
throw new DaoException("Cannot insert block_status with null value.");
}
try {
Connection conn = DataSourceUtils.doGetConnection(dataSource);
PreparedStatement ps =
conn.prepareStatement(
"insert into block_status(`id`,`id2`,`shipment_id`,`declaration_no`,`status`,`remark`,`block_date_time`,`unblock_date_time`,`company_code`,`company_type`,`user_block`,`mawb`,`hawb`,`flight_no`,`flight_date`,`user_unblock`,`modified_date_time`,`auto_block_profile_id`) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
Statement.RETURN_GENERATED_KEYS);
BlockStatusDaoImpl.setPreparedStatementValues(ps, blockStatus);
ps.setNull(1, java.sql.Types.INTEGER);
ps.execute();
int id = -1;
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
id = rs.getInt(1);
}
rs.close();
ps.close();
logger.info("DB:INSERT block_status[id=" + id + "]");
return id;
} catch (SQLException e) {
throw new DaoException(e);
}
}
/*
* Add a computer to the database, and return the id auto incremented of the
* computer added
*/
public Long add(Computer computer) throws SQLException {
logger.debug("Enterring add in ComputerDAO.");
Connection connection = DataSourceUtils.getConnection(datasource);
Long id = null;
String query =
"INSERT INTO `computer-database-db`.`computer` (name,introduced,discontinued,company_id) VALUES (?,?,?,?);";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, computer.getName());
if (computer.getIntroduced() == null) {
statement.setDate(2, null);
} else statement.setDate(2, new java.sql.Date(computer.getIntroduced().getMillis()));
if (computer.getDiscontinued() == (null)) {
statement.setDate(3, null);
} else statement.setDate(3, new java.sql.Date(computer.getDiscontinued().getMillis()));
if (computer.getCompany().getId().equals((0L))) {
statement.setString(4, null);
} else statement.setLong(4, computer.getCompany().getId());
statement.executeUpdate();
ResultSet resultSet = null;
resultSet = statement.getGeneratedKeys();
if (resultSet != null) {
resultSet.next();
id = Long.parseLong(resultSet.getString(1));
}
if (statement != null) statement.close();
if (resultSet != null) resultSet.close();
logger.debug("Leaving add in ComputerDAO.");
return id;
}
/** 关闭数据库连接 */
private void close() {
if (conn != null) {
JdbcUtils.closeResultSet(rs);
JdbcUtils.closeStatement(ps);
DataSourceUtils.releaseConnection(conn, ds);
}
ps = null;
conn = null;
}
/**
* This implementation calls {@link DataSourceUtils#doCloseConnection}, checking against a {@link
* org.springframework.jdbc.datasource.SmartDataSource}.
*/
@Override
public void closeConnection(Connection con) throws SQLException {
try {
DataSourceUtils.doCloseConnection(con, this.dataSourceToUse);
} catch (SQLException ex) {
JDBCExceptionReporter.logExceptions(ex);
throw ex;
}
}
/** Release connection. */
public void releaseConnection(Connection conn) {
if (conn != null) {
if (conn instanceof DelegatingConnection) {
conn = (Connection) ((DelegatingConnection) conn).getInnerDelegate();
}
DataSourceUtils.releaseConnection(conn, this.dataSource);
}
}
public static void main(String[] args) {
DbInstance dbinstance =
new DbInstance(
false, // pooled
"null", // datasourcePath
"<REPLACE_WITH_DB_URL>", // dbUrl
"qsmaster",
null,
"com.mysql.jdbc.Driver",
"lsstdb");
DataSource ds = JdbcFactory.getDataSource(dbinstance);
String sql = "select * from DeepSource where qserv_areaspec_box(0.4, 1.05, 0.5, 1.15)";
Connection conn = null;
try {
conn = DataSourceUtils.getConnection(ds);
long cTime = System.currentTimeMillis();
Statement stmt = null;
try {
stmt = conn.createStatement();
_log.briefDebug("Executing SQL query: " + sql);
// ResultSet rs = stmt.executeQuery(sql);
// _log.briefDebug ("SELECT took "+(System.currentTimeMillis()-cTime)+"ms");
File file = new File("/tmp/lsstTest.tbl");
IpacTableExtractor.query(null, ds, file, 10000, sql);
_log.briefDebug("SELECT took " + (System.currentTimeMillis() - cTime) + "ms");
} catch (Exception e) {
System.out.println("Exception " + e.getMessage());
} finally {
closeStatement(stmt);
}
} catch (Exception e) {
System.out.println("Exception " + e.getMessage());
e.printStackTrace();
} finally {
if (conn != null) {
DataSourceUtils.releaseConnection(conn, ds);
}
}
}
public void close() throws SQLException {
insertAndUpdateRecordStmt.close();
updateRecordStmt.close();
selectConflictsStmt.close();
updateValidToStmt.close();
updateValidFromStmt.close();
DataSourceUtils.releaseConnection(connection, datasource);
System.gc();
}
/*
* Delete a computer from the database
*/
public void delete(Computer computer) throws SQLException {
logger.debug("Enterring delete in ComputerDAO.");
Connection connection = DataSourceUtils.getConnection(datasource);
String query = "DELETE FROM `computer-database-db`.`computer` WHERE id=?;";
PreparedStatement statement = connection.prepareStatement(query);
statement.setLong(1, computer.getId());
statement.executeUpdate();
if (statement != null) statement.close();
logger.debug("Leaving delete in ComputerDAO.");
}
public static int getRowCount(DataSource dataSource, String... tableNames) {
Connection connection = DataSourceUtils.getConnection(dataSource);
int count = 0;
try {
for (String tableName : tableNames) {
ResultSet resultSet =
connection.createStatement().executeQuery("SELECT count(*) AS COUNT FROM " + tableName);
while (resultSet.next()) {
count = count + resultSet.getInt(1); // COUNT
}
}
} catch (SQLException e) {
throw new RuntimeException("Failed to execute database getRowCount", e);
} finally {
if (connection != null) {
DataSourceUtils.releaseConnection(connection, dataSource);
}
}
log.debug("Execute database getRowCount... {}", count);
return count;
}
/**
* 获取本地的Connection对象
*
* @return
*/
public Connection getNativeConn() {
// 从当前线程绑定的数据连接获取连接
Connection conn = DataSourceUtils.getConnection(this.getJdbcTemplate().getDataSource());
try {
conn = this.getJdbcTemplate().getNativeJdbcExtractor().getNativeConnection(conn);
} catch (SQLException e) {
e.printStackTrace();
return null;
}
return conn;
}
public void upgradeLevels() throws Exception {
TransactionSynchronizationManager.initSynchronization();
Connection c = DataSourceUtils.getConnection(dataSource);
c.setAutoCommit(false);
try {
List<User> users = userDao.getAll();
for (User user : users) {
if (canUpgradeLevel(user)) {
upgradeLevel(user);
}
}
c.commit();
} catch (Exception e) {
c.rollback();
throw e;
} finally {
DataSourceUtils.releaseConnection(c, dataSource);
TransactionSynchronizationManager.unbindResource(this.dataSource);
TransactionSynchronizationManager.clearSynchronization();
}
}
public static String[] getTableNames(DataSource dataSource) {
Connection connection = DataSourceUtils.getConnection(dataSource);
List<String> tableNames = new ArrayList<String>();
try {
ResultSet resultSet =
connection.getMetaData().getTables(null, null, "%", new String[] {"TABLE"});
while (resultSet.next()) {
String tableName = resultSet.getString(3);
if (!"SCHEMA_VERSION".equalsIgnoreCase(tableName)) {
tableNames.add(resultSet.getString(3)); // TABLE_NAME
}
}
} catch (SQLException e) {
throw new RuntimeException("Failed to execute database getTableNames", e);
} finally {
if (connection != null) {
DataSourceUtils.releaseConnection(connection, dataSource);
}
}
log.debug("Execute database getTableNames... {}", tableNames);
return tableNames.toArray(new String[tableNames.size()]);
}
public Number getNumberBySQL(String sql, Object[] values) {
this.log("queryForNumber sql [ " + sql + " ]");
PreparedStatement ps = null;
ResultSet rs = null;
Connection con = this.getCurrentConnection();
try {
ps = con.prepareStatement(sql);
if (values != null) {
for (int i = 0; i < values.length; i++) {
ps.setObject(i + 1, values[i]);
}
}
rs = ps.executeQuery();
int size = 0;
Number res = 0;
while (rs.next()) {
res = (Number) rs.getObject(1);
size++;
}
if (size == 0) {
return res;
}
if (size > 1) {
throw new IncorrectResultSizeDataAccessException(1, size);
}
return res;
} catch (SQLException e) {
JdbcUtils.closeStatement(ps);
ps = null;
DataSourceUtils.releaseConnection(con, getDataSource());
con = null;
e.printStackTrace();
throw getExceptionTranslator().translate("StatementCallback", sql, e);
} finally {
JdbcUtils.closeResultSet(rs);
JdbcUtils.closeStatement(ps);
DataSourceUtils.releaseConnection(con, getDataSource());
}
}
@Override
public int deleteById(Integer id) throws DaoException {
try {
Connection conn = DataSourceUtils.doGetConnection(dataSource);
PreparedStatement ps = conn.prepareStatement("delete from block_status where id=?");
ps.setInt(1, id);
int count = ps.executeUpdate();
ps.close();
logger.info("DB:DELETE block_status[id=" + id + "] => " + count);
return count;
} catch (SQLException e) {
throw new DaoException(e);
}
}
/**
* 执行脚本并获得查询结果集
*
* @return
*/
private ResultSet executeQuery() {
if (rs == null) {
try {
conn = DataSourceUtils.getConnection(ds);
ps = conn.prepareStatement(sql);
this.prepareValues(ps, params, values);
rs = ps.executeQuery();
while (index < startResult && rs.next()) index++;
} catch (SQLException e) {
close();
}
}
return rs;
}
/*
* Return the number of computers with a specific company in the database
*/
public Long countByCompanyName(String name) throws SQLException {
logger.debug("Entering countByCompanyName in ComputerDAO.");
Connection connection = DataSourceUtils.getConnection(datasource);
String query =
"SELECT COUNT(*) FROM `computer-database-db`.`computer` AS computer LEFT OUTER JOIN `computer-database-db`.`company` AS company ON computer.company_id=company.id WHERE company.name=? ;";
Long nbrComputers = null;
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, name);
ResultSet resultSet = statement.executeQuery();
resultSet.next();
nbrComputers = resultSet.getLong(1);
if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
logger.debug("Leaving countByCompanyName in ComputerDAO.");
return nbrComputers;
}
/*
* Return the number of computers in the database
*/
public Long count() throws SQLException {
logger.debug("Enterring count in ComputerDAO.");
Connection connection = DataSourceUtils.getConnection(datasource);
System.out.println(connection);
String query = "SELECT COUNT(*) FROM `computer-database-db`.computer ;";
PreparedStatement statement = connection.prepareStatement(query);
ResultSet resultSet = statement.executeQuery();
Long nbrComputers = null;
resultSet.next();
nbrComputers = resultSet.getLong(1);
if (resultSet != null) resultSet.close();
if (statement != null) statement.close();
logger.debug("Leaving count in ComputerDAO.");
return nbrComputers;
}
@Test
public void testCreateTable() throws SQLException {
String sqlCreateTable =
"CREATE TABLE Item ( "
+ " id BIGINT, "
+ " property1 VARCHAR(255), "
+ " property2 VARCHAR(255) "
+ "); ";
Connection c = DataSourceUtils.getConnection(dataSource);
Statement statement = c.createStatement();
statement.addBatch(sqlCreateTable);
statement.executeBatch();
if (statement != null) {
statement.close();
}
}
public DatabaseTableWrapper(DataSource datasource, Class<T> type) throws SQLException {
this.tablename = Entities.getEntityTypeDisplayName(type);
this.type = type;
this.datasource = datasource;
this.connection =
DataSourceUtils.getConnection(
datasource); // gets the transactionmanager's connection, not just a new one
this.idMethod = Entities.getIdMethod(type);
this.outputMethods = Entities.getOutputMethods(type);
this.notUpdatedColumns = findNotUpdatedColumns();
this.insertRecordStmt = prepareInsertStatement();
this.insertAndUpdateRecordStmt = prepareInsertAndUpdateStatement();
this.updateRecordStmt = prepareUpdateStatement();
this.selectConflictsStmt = prepareSelectConflictsStatement();
this.updateValidToStmt = prepareUpdateValidToStatement();
this.updateValidFromStmt = prepareUpdateValidFromStatement();
}
@Override
public int count() throws DaoException {
try {
Connection conn = DataSourceUtils.doGetConnection(dataSource);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select count(*) from block_status");
int count = -1;
if (rs.next()) {
count = rs.getInt(1);
}
rs.close();
stmt.close();
logger.debug("DB:COUNT block_status => " + count);
return count;
} catch (SQLException e) {
throw new DaoException(e);
}
}
/*
* Edit a computer from the database
*/
public void edit(Computer computer) throws SQLException {
logger.debug("Enterring edit in ComputerDAO.");
Connection connection = DataSourceUtils.getConnection(datasource);
String query =
"UPDATE computer SET name =?,introduced=?,discontinued=?,company_id=? WHERE id=?;";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, computer.getName());
if (computer.getIntroduced() == null) {
statement.setDate(2, null);
} else statement.setDate(2, new java.sql.Date(computer.getIntroduced().getMillis()));
if (computer.getDiscontinued() == (null)) {
statement.setDate(3, null);
} else statement.setDate(3, new java.sql.Date(computer.getDiscontinued().getMillis()));
if (computer.getCompany().getId().equals((0L))) {
statement.setString(4, null);
} else statement.setLong(4, computer.getCompany().getId());
statement.setLong(5, computer.getId());
statement.executeUpdate();
if (statement != null) statement.close();
logger.debug("Leaving edit in ComputerDAO.");
}