private void testIdentity(Connection conn) throws SQLException {
Statement stat = conn.createStatement();
stat.execute("CREATE SEQUENCE SEQ");
stat.execute("CREATE TABLE TEST(ID INT)");
PreparedStatement prep;
prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)");
prep.execute();
ResultSet rs = prep.getGeneratedKeys();
rs.next();
assertEquals(1, rs.getInt(1));
assertFalse(rs.next());
prep =
conn.prepareStatement(
"INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", Statement.RETURN_GENERATED_KEYS);
prep.execute();
rs = prep.getGeneratedKeys();
rs.next();
assertEquals(2, rs.getInt(1));
assertFalse(rs.next());
prep = conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new int[] {1});
prep.execute();
rs = prep.getGeneratedKeys();
rs.next();
assertEquals(3, rs.getInt(1));
assertFalse(rs.next());
prep =
conn.prepareStatement("INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)", new String[] {"ID"});
prep.execute();
rs = prep.getGeneratedKeys();
rs.next();
assertEquals(4, rs.getInt(1));
assertFalse(rs.next());
prep =
conn.prepareStatement(
"INSERT INTO TEST VALUES(NEXT VALUE FOR SEQ)",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY,
ResultSet.HOLD_CURSORS_OVER_COMMIT);
prep.execute();
rs = prep.getGeneratedKeys();
rs.next();
assertEquals(5, rs.getInt(1));
assertFalse(rs.next());
stat.execute("DROP TABLE TEST");
}
public Container insert(Connection connection, String path) throws SQLException {
Container container = selectByPath(connection, path);
if (container != null) {
return container;
}
PreparedStatement statement =
connection.prepareStatement(Q_INSERT, Statement.RETURN_GENERATED_KEYS);
try {
int param = 0;
statement.setString(++param, path);
statement.executeUpdate();
ResultSet result = statement.getGeneratedKeys();
try {
result.next();
container = new Container(result.getInt(1), path);
H2Cache.addContainer(container);
} finally {
result.close();
}
} finally {
statement.close();
}
return container;
}
public Integer add(WheelsBCD entity) {
Integer generatedId = null;
ResultSet resultSet = null;
Connection connection = null;
PreparedStatement preparedStatement = null;
if (checkEntity(entity)) {
try {
connection = connectionPool.getConnection();
preparedStatement =
(PreparedStatement)
connection.prepareStatement(addRequest, PreparedStatement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, entity.getValue());
preparedStatement.execute();
resultSet = preparedStatement.getGeneratedKeys();
if (resultSet.next()) {
generatedId = resultSet.getInt(1);
}
} catch (SQLException ex) {
generatedId = null;
} finally {
closeAll(resultSet, preparedStatement, connection);
}
}
return generatedId;
}
private int inserir(Endereco e) {
int status = -1;
Connection con = null;
PreparedStatement pstm = null;
try {
con = ConnectionFactory.getConnection();
pstm = con.prepareStatement(INSERT, Statement.RETURN_GENERATED_KEYS);
pstm.setString(1, e.getRua());
pstm.execute();
try (ResultSet rs = pstm.getGeneratedKeys()) {
rs.next();
status = rs.getInt(1);
}
} catch (Exception ex) {
JOptionPane.showMessageDialog(null, "Erro ao inserir um endereço: " + ex.getMessage());
} finally {
}
try {
ConnectionFactory.closeConnection(con, pstm);
} catch (Exception ex) {
JOptionPane.showMessageDialog(null, "Erro ao fechar conexão: " + ex.getMessage());
}
return status;
}
/**
* insert
*
* @param user
* @return user The mistake inserted
*/
@Override
public User insert(User user) throws UserException {
Connection c = MysqlDAOFactory.createConnection();
if (user.getCreatedAt() == null) user.setCreatedAt(new Date().getTime());
PreparedStatement pstmt;
try {
pstmt = c.prepareStatement(INSERT);
pstmt.setString(1, user.getEmail());
pstmt.setTimestamp(2, user.getCreateAtTime());
pstmt.executeUpdate();
ResultSet rset = pstmt.getGeneratedKeys();
rset.next();
Long idGenerated = rset.getLong(1);
user.setId(idGenerated);
pstmt.close();
c.close();
return user;
} catch (SQLException e) {
throw new UserException(e.getMessage());
}
}
public static long appendedWrite(Summary sum, String runId) throws Exception {
String className = sum.getClass().getName();
PreparedStatement pstmt = conn.prepareStatement(WRITE_OBJECT_SQL);
sum.setLastUpdated();
Object sum2 = (Object) sum;
// Set input parameters
pstmt.setString(1, runId);
pstmt.setObject(2, sum2);
pstmt.setInt(3, sum.getState());
pstmt.executeUpdate();
// get the generated key for the id
ResultSet rs = pstmt.getGeneratedKeys();
int id = -1;
if (rs.next()) {
id = rs.getInt(1);
}
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
} catch (Exception E) {
metrixLogger.log.log(
Level.SEVERE, "Error in closing resource sets of SQL Connection. {0}", E.toString());
}
return id;
}
/** Get id after insert method getGeneratedKey(). */
private Object getGeneratedKey(PreparedStatement pst) throws SQLException {
ResultSet rs = pst.getGeneratedKeys();
Object id = null;
if (rs.next()) id = rs.getObject(1);
rs.close();
return id;
}
@Override
public int addComment(String comment, int userId) {
SqlUtilities.jbdcUtil();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
int id = 0;
try {
connection =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/fourscorepicks", "fourscorepicks", "fourscorepicks");
String query =
"INSERT INTO comments (comment_text, user_id, date_posted) " + "VALUES (?, ?, now())";
preparedStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, comment);
preparedStatement.setInt(2, userId);
preparedStatement.executeUpdate();
resultSet = preparedStatement.getGeneratedKeys();
if (resultSet.next()) {
id = resultSet.getInt(1);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
SqlUtilities.closePreparedStatement(preparedStatement);
SqlUtilities.closeConnection(connection);
}
return id;
}
public int registerAddressOfUser(MySqlHelper helper, User user, int userId) {
String query =
"insert into address(UserId,AddressLine1, AddressLine2, City, State, ZipCode, Lattitude,Longitude) values(?, ?, ?, ?, ?, ?, ?, ?)";
try {
java.sql.PreparedStatement addressPreparedStatement =
helper.conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
addressPreparedStatement.setInt(1, userId);
addressPreparedStatement.setString(2, user.getAddress().getAddressLine1());
addressPreparedStatement.setString(3, user.getAddress().getAddressLine2());
addressPreparedStatement.setString(4, user.getAddress().getCity());
addressPreparedStatement.setString(5, user.getAddress().getState());
addressPreparedStatement.setString(6, user.getAddress().getZipCode());
addressPreparedStatement.setString(7, user.getAddress().getLattitude());
addressPreparedStatement.setString(8, user.getAddress().getLongitude());
addressPreparedStatement.executeUpdate();
ResultSet rs = addressPreparedStatement.getGeneratedKeys();
if (rs.next()) {
int last_inserted_id = rs.getInt(1);
return last_inserted_id;
}
} catch (Exception ex) {
ex.printStackTrace();
}
return -1;
}
public static PublicKeyRecord createNew(KeyPair kp)
throws NoSuchAlgorithmException, InvalidKeySpecException {
KeyFactory fact = KeyFactory.getInstance("RSA");
RSAPublicKeySpec pub = fact.getKeySpec(kp.getPublic(), RSAPublicKeySpec.class);
int id = 0;
try {
Connection conn = DatabaseConnection.getConn();
String sql = "insert into publickey modulus = ?, exponent = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, pub.getModulus().toString());
stmt.setString(2, pub.getPublicExponent().toString());
stmt.executeUpdate();
ResultSet generatedKeys = stmt.getGeneratedKeys();
if (generatedKeys.next()) {
id = generatedKeys.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return get(id);
}
public int addAnnotationLogEntry(
String sid, int projectId, int referenceId, Action action, Status status)
throws SQLException, RemoteException, SessionExpiredException {
String user = SessionController.getInstance().getUserForSession(sid);
Timestamp sqlDate = SQLUtils.getCurrentTimestamp();
TableSchema table = MedSavantDatabase.VariantpendingupdateTableSchema;
InsertQuery query = new InsertQuery(table.getTable());
query.addColumn(
table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_PROJECT_ID), projectId);
query.addColumn(
table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_REFERENCE_ID), referenceId);
query.addColumn(
table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_ACTION),
AnnotationLog.actionToInt(action));
query.addColumn(
table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_STATUS),
AnnotationLog.statusToInt(status));
query.addColumn(
table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_TIMESTAMP), sqlDate);
query.addColumn(table.getDBColumn(VariantPendingUpdateTableSchema.COLUMNNAME_OF_USER), user);
Connection c = ConnectionController.connectPooled(sid);
PreparedStatement stmt = c.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS);
stmt.execute();
ResultSet rs = stmt.getGeneratedKeys();
rs.next();
c.close();
return rs.getInt(1);
}
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 addRiji(Diary diary) throws Exception {
int id = 0;
Connection conn = null;
PreparedStatement stat = null;
ResultSet rs = null;
try {
conn = DBConection.openConnection();
stat =
conn.prepareStatement(
"insert into tb_diary(dtime,weekday,weather,represent,author) values(?,?,?,?,?)",
Statement.RETURN_GENERATED_KEYS);
stat.setString(1, diary.getDtime());
stat.setString(2, diary.getWeekday());
stat.setString(3, diary.getWeather());
stat.setString(4, diary.getRepresent());
stat.setString(5, diary.getAuthor());
int result = stat.executeUpdate();
if (result > 0) {
rs = stat.getGeneratedKeys();
if (rs.next()) {
id = rs.getInt(1);
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBConection.closeConnection(conn, stat, rs);
}
return id;
}
public void inserir(Endereco endereco) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
try {
int i = 0;
conn = daoHelper.getConnectionFromContext();
pstmt =
conn.prepareStatement(
"INSERT INTO ENDERECO (ENDERECO,CIDADE,CEP,BAIRRO) VALUES (?,?,?,?) ",
PreparedStatement.RETURN_GENERATED_KEYS);
pstmt.setString(++i, endereco.getEndereco());
pstmt.setString(++i, endereco.getCidade());
pstmt.setString(++i, endereco.getCep());
pstmt.setString(++i, endereco.getBairro());
pstmt.executeUpdate();
rset = pstmt.getGeneratedKeys();
if (rset.next()) {
endereco.setId(rset.getLong(1));
}
} catch (SQLException e) {
throw new CreateDaoException("Nao foi possivel armazenar Endereco " + endereco, e);
}
}
/**
* Add recruitment request to permanent store.
*
* @param toAdd request to be added
* @return id of added request or -1 if unsuccessful
*/
public int addRecruitmentRequest(RecruitmentRequestBean toAdd) {
String insertStatement =
"insert into recruitmentrequests (requestdep, requestdate, jobtitle, contracttype, yearsofexperience, jobdescription, status) values (?,?,?,?,?,?,?);";
try {
PreparedStatement statement =
connection.prepareStatement(insertStatement, Statement.RETURN_GENERATED_KEYS);
statement.setNString(1, toAdd.getRequestDepartment());
statement.setDate(2, new java.sql.Date(toAdd.getRequestDate().getTime()));
statement.setNString(3, toAdd.getJobTitle());
statement.setNString(4, toAdd.getContractType());
statement.setInt(5, toAdd.getYearsOfExperience());
statement.setNString(6, toAdd.getJobDescription());
statement.setNString(7, "active");
statement.executeUpdate();
ResultSet rs = statement.getGeneratedKeys();
if (rs.first()) {
return rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
}
return -1;
}
public void register(RegPanel reg) {
if (this.surname.trim().isEmpty()) {
JOptionPane.showMessageDialog(reg, "Surname required", "Error", JOptionPane.ERROR_MESSAGE);
} else if (this.names.trim().isEmpty()) {
JOptionPane.showMessageDialog(reg, "Names required", "Error", JOptionPane.ERROR_MESSAGE);
} else if (this.tmp_ID.trim().isEmpty()) {
JOptionPane.showMessageDialog(reg, "ID Number required", "Error", JOptionPane.ERROR_MESSAGE);
} else if (this.password1.trim().isEmpty()) {
JOptionPane.showMessageDialog(reg, "Password required", "Error", JOptionPane.ERROR_MESSAGE);
} else if (this.password2.trim().isEmpty()) {
JOptionPane.showMessageDialog(
reg, "Confirmation Password required", "Error", JOptionPane.ERROR_MESSAGE);
} else if (!this.password1.trim().equals(this.password2)) {
JOptionPane.showMessageDialog(
reg, "Unmatching Passwords", "Error", JOptionPane.ERROR_MESSAGE);
} else {
this.password = Connector.hash(this.password1);
Connection conn = Connector.createConnection();
PreparedStatement stmt;
int agent_id;
try {
this.ID = Long.parseLong(this.tmp_ID);
String sql = "INSERT INTO agents (ID_NO,sname,names,password) VALUES (?,?,?,?)";
stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
stmt.setLong(1, this.ID);
stmt.setString(2, this.surname);
stmt.setString(3, this.names);
stmt.setString(4, this.password);
stmt.executeUpdate();
ResultSet res = stmt.getGeneratedKeys();
if (res.next()) {
agent_id = res.getInt(1);
Agent ag = new Agent(agent_id, conn);
new SessionController(ag);
reg.setVisible(false);
new Start().setVisible(true);
} else {
System.out.println("Error getting insert id");
System.exit(1);
}
} catch (SQLException e) {
JOptionPane.showMessageDialog(reg, e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
} catch (NumberFormatException e) {
JOptionPane.showMessageDialog(
reg,
"Please enter the ID Number in correct format",
"Error",
JOptionPane.ERROR_MESSAGE);
}
}
}
public void save() throws SQLException {
try (Connection connection = DbHelper.getConnection()) {
if (id == -1) {
Contact.LOGGER.debug("Adding new contact: {}", this);
final String sql = "INSERT INTO contacts(name, contacts) VALUES(?, ?)";
try (PreparedStatement pstmt =
connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
pstmt.setString(1, name);
pstmt.setString(2, contacts);
pstmt.execute();
try (final ResultSet rs = pstmt.getGeneratedKeys()) {
rs.next();
id = rs.getLong(1);
}
}
} else {
Contact.LOGGER.debug("Updating existing contact: {}", this);
final String sql = "UPDATE contacts SET name = ?, contacts = ? WHERE id = ?";
try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
pstmt.setString(1, name);
pstmt.setString(2, contacts);
pstmt.setLong(3, id);
pstmt.execute();
}
}
}
}
public String createVideo(String videoname, String videourl) throws SQLException {
PreparedStatement preparedStatement =
mysqlCon.prepareStatement("SELECT * FROM `IKSvideotag`.`video` WHERE `url`=?");
preparedStatement.setString(1, videourl);
ResultSet dataSet = preparedStatement.executeQuery();
String videoid = "";
if (dataSet.next()) {
videoid = dataSet.getString(1);
preparedStatement.close();
preparedStatement =
mysqlCon.prepareStatement("UPDATE `IKSvideotag`.`video` SET `name`=? WHERE `url`=?");
preparedStatement.setString(1, videoname);
preparedStatement.setString(2, videourl);
preparedStatement.executeUpdate();
preparedStatement.close();
} else {
preparedStatement.close();
preparedStatement =
mysqlCon.prepareStatement(
"INSERT INTO `IKSvideotag`.`video` (`name`,`url`) VALUES (?, ?)",
Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, videoid);
preparedStatement.setString(2, videourl);
preparedStatement.executeUpdate();
ResultSet rs = preparedStatement.getGeneratedKeys();
if (rs.next()) {
videoid = String.valueOf(rs.getInt(1));
}
preparedStatement.close();
}
return videoid;
}
@Test
public void testGetGeneratedKeys() {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "insert into user (name,email,birth) values(?,?,?)";
try {
// 使用jdbc获取数据库连接
// con=JDBCUtil.getConnection();
// 使用c3p0连接池获取数据库连接
con = JDBCUtil.getC3p0Con();
ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, "Jerry");
ps.setString(2, "*****@*****.**");
ps.setDate(3, new Date(new java.util.Date().getTime()));
ps.executeUpdate();
rs = ps.getGeneratedKeys();
if (rs.next()) {
System.out.println(rs.getInt(1));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtil.close(null, null, ps, con);
}
}
/*
* 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;
}
/**
* @param conn
* @param fingerprint
* @param sampleID
* @return
* @throws SQLException
*/
private static void insertSampleSet(Connection conn, Fingerprint fingerprint, Integer sampleID)
throws SQLException {
if (fingerprint.getSampleSetID() == null) {
/*
* Insert whole new SampleSetID.
*/
String query = "INSERT INTO `SampleSets`(`SampleID`) VALUES(?);";
PreparedStatement insertSampleSet =
conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
insertSampleSet.setInt(1, sampleID);
insertSampleSet.execute();
ResultSet rs = insertSampleSet.getGeneratedKeys();
if (rs.next()) {
fingerprint.setSampleSetID(rs.getInt(1));
}
rs.close();
insertSampleSet.close();
} else {
/*
* Insert new SampleID for existing SampleSetID.
*/
String query = "INSERT INTO `SampleSets`(`SampleSetID`,`SampleID`) VALUES(?, ?);";
PreparedStatement insertSampleSet = conn.prepareStatement(query);
insertSampleSet.setInt(1, fingerprint.getSampleSetID());
insertSampleSet.setInt(2, sampleID);
insertSampleSet.execute();
insertSampleSet.close();
}
}
@Override
public EntidadBancaria insert(EntidadBancaria entidadBancaria) {
try {
Connection connection;
connection = connectionFactory.getConnection();
String sql = "INSERT INTO entidadBancaria VALUES (null,?,?,?,?,?);";
PreparedStatement preparedStatement = connection.prepareStatement(sql, RETURN_GENERATED_KEYS);
preparedStatement.setString(1, entidadBancaria.getNombre());
preparedStatement.setString(2, entidadBancaria.getCodigoEntidad());
java.sql.Date fechaSQL = new java.sql.Date(entidadBancaria.getFechaCreacion().getTime());
preparedStatement.setDate(3, fechaSQL);
preparedStatement.setString(4, entidadBancaria.getDireccion());
preparedStatement.setString(5, entidadBancaria.getCif());
preparedStatement.execute();
ResultSet resulclave = preparedStatement.getGeneratedKeys();
if (resulclave.next()) {
entidadBancaria.setIdEntidadBancaria(resulclave.getInt(1));
} else {
throw new RuntimeException("Error SQL");
}
connectionFactory.close(connection);
return entidadBancaria;
} catch (SQLException ex) {
throw new RuntimeException("Error SQL: " + ex.getMessage());
}
}
/**
* Extract the generated key for JDBC drivers that support getGeneratedKeys()
*
* <p>{@inheritDoc}
*/
public Long getGeneratedKey(PreparedStatement pstmt, String sql) throws SQLException {
ResultSet keys = pstmt.getGeneratedKeys();
if (keys.next()) {
return Long.valueOf(keys.getLong(1));
}
return null;
}
long insertMethod(String[] split, String mname) throws SQLException {
PreparedStatement insertapp;
long key = -1;
String insertString =
"INSERT INTO METHODS(CLASSID,NAME) VALUES("
+ "((select ID from CLASSES where package = '"
+ split[0]
+ "' AND name='"
+ split[1]
+ "')),'"
+ mname
+ "')";
insertapp = conn.prepareStatement(insertString, Statement.RETURN_GENERATED_KEYS);
ResultSet generatedKeys;
insertapp.executeUpdate();
generatedKeys = insertapp.getGeneratedKeys();
if (generatedKeys.next()) {
key = generatedKeys.getLong(1);
}
// System.out.println("Method inserted");
return key;
}
/**
* 插入实体类返回自增长id
*
* @param entity
* @return
*/
public Integer insertBackID(T entity) {
Integer auto_id = -1;
ResultSet rs = null;
PreparedStatement pst = null;
Connection connection = null;
try {
List<Object> sList =
DBUtils.getInstance().createInsertParms(getTableName(), entity, getTablePK());
String sql = sList.get(sList.size() - 1).toString();
System.out.println(sql);
connection = DBManager.getConnection();
pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < sList.size() - 1; i++) {
setParam(pst, sList.get(i), i);
}
pst.executeUpdate();
rs = pst.getGeneratedKeys();
if (rs.next()) {
auto_id = rs.getInt(1);
}
rs.close();
pst.close();
} catch (Exception e) {
e.printStackTrace();
// TODO: handle exception
} finally {
DBManager.freeConnection(connection);
}
return auto_id;
}
public static boolean addComment(Comment comment) {
ResultSet rs;
try {
Connection conn = DbManager.getConnection();
String sql =
"INSERT INTO comment(user_Id , content , writtenTime , diagram_Id) VALUES(?,?,?,?);";
PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
pstmt.setInt(1, comment.getUserId());
pstmt.setString(2, comment.getContent());
pstmt.setString(3, comment.getCommentTime());
pstmt.setInt(4, comment.getDiagramId());
pstmt.executeUpdate();
// Get and set the auto-generated PK
rs = pstmt.getGeneratedKeys();
if (rs.next()) {
int newId = rs.getInt(1);
comment.setCommentId(newId);
}
rs.close();
pstmt.close();
conn.close();
} catch (SQLException ex) {
Logger.getLogger(CommentDAO.class.getName()).log(Level.SEVERE, null, ex);
}
return true;
}
// 在insert中是否有自动生成内容生成，isauto==true,表示要获得自动生成的键值
public List<Integer> executeUpdate(String sql, List<Object> values, boolean isauto)
throws myException {
log.info("daoTemplate执行操作性语句:" + sql);
int count = 0;
try {
pre =
conn.prepareStatement(
sql,
(isauto == true) ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS);
int i = 1;
if (values != null) {
for (Object o : values) {
pre.setObject(i, o);
i++;
}
}
count = pre.executeUpdate();
if (!isauto) {
close(null, pre, null);
List<Integer> ids = new ArrayList<Integer>();
ids.add(new Integer(count));
return ids;
} else {
List<Integer> ids = new ArrayList<Integer>();
ResultSet rs = pre.getGeneratedKeys();
while (rs.next()) {
ids.add(rs.getInt(1));
}
close(rs, pre, null);
return ids;
}
} catch (Exception e) {
throw new myException(e);
}
}
/** Create and store a new manuscript. Generates a new manuscript id. */
public Manuscript(String repository, String archive, String collection, String city)
throws SQLException {
String query =
"insert into manuscript (repository, archive, msIdentifier,city) values(?,?,?,?)";
Connection j = null;
PreparedStatement ps = null;
try {
j = DatabaseWrapper.getConnection();
ps = j.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);
ps.setString(1, repository);
ps.setString(2, archive);
ps.setString(3, collection);
ps.setString(4, city);
ps.execute();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
this.id = rs.getInt(1);
this.collection = collection;
this.city = city;
this.repository = repository;
this.archive = archive;
}
} finally {
DatabaseWrapper.closeDBConnection(j);
DatabaseWrapper.closePreparedStatement(ps);
}
}
private int insertPageContent(
String text, int resultPageNumber, String searchEngineName, int crawlRecordId) {
try {
PreparedStatement pstmt =
con.prepareStatement(
"INSERT INTO pagecontent (htmlcontent, resultPageNumber, searchEngineName, crawlRecordId,queryText) VALUES (?,?,?,?,?);",
Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, text);
pstmt.setInt(2, resultPageNumber);
pstmt.setString(3, searchEngineName);
pstmt.setInt(4, crawlRecordId);
pstmt.setString(5, query);
pstmt.executeUpdate();
java.sql.ResultSet generatedKeys = pstmt.getGeneratedKeys();
if (generatedKeys.next()) {
return generatedKeys.getInt(1);
}
pstmt.close();
} catch (Exception ex) {
String a = "";
String b = "";
}
return 0;
}
/**
* This method updates an activity from the database
*
* @param name
* @param description
* @return the updated Activity's id
*/
public static Integer updateActivity(Integer id, String name, String description) {
Integer idToReturn = null;
try {
Connection connect = Connect.getInstance().getConnection();
PreparedStatement instruction =
connect.prepareStatement(queryUpdateOne, Statement.RETURN_GENERATED_KEYS);
instruction.setString(1, name);
instruction.setString(2, description);
instruction.setInt(3, id);
int affectedRows = instruction.executeUpdate();
connect.commit();
if (affectedRows == 0) {
throw new SQLException("Updating an activity failed, no rows affected.");
}
try (ResultSet generatedKeys = instruction.getGeneratedKeys()) {
if (generatedKeys.next()) {
idToReturn = generatedKeys.getInt(1);
} else {
throw new SQLException("Updating an activity failed, no ID obtained.");
}
}
connect.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
Connect.getInstance().closeConnection();
}
return idToReturn;
}
