@Override
public long addCompanySales(CompanySales companySales) {
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
String sql =
"insert into company_sales (company_id, file_name, sales_date, create_time)"
+ " values (?, ?, ?, ?)";
PreparedStatement ps =
connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setLong(1, companySales.getCompanyId() == null ? 0 : companySales.getCompanyId());
ps.setString(2, companySales.getFileName() == null ? "" : companySales.getFileName());
ps.setString(3, companySales.getSalesDate() == null ? "" : companySales.getSalesDate());
ps.setTimestamp(
4,
companySales.getCreateTime() == null
? new Timestamp(System.currentTimeMillis())
: companySales.getCreateTime());
return ps;
}
},
keyHolder);
logger.debug("addCompanySales result : {}", keyHolder.getKey().longValue());
return keyHolder.getKey().longValue();
}
/**
* @param entity
* @param sql
* @param params
* @return 下午1:11:38 created by Darwin(Tianxin)
*/
@SuppressWarnings("unchecked")
private ENTITY createAndFetchKey(ENTITY entity, final String sql, final List<Object> params) {
KeyHolder keyHolder = new GeneratedKeyHolder();
recordLog(sql);
// 执行操作
int rowCount =
this.jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement ps = connection.prepareStatement(sql);
int index = 1;
for (Object param : params) ps.setObject(index++, param);
return ps;
}
},
keyHolder);
// 如果插入成功则获取keyHolder中的key
if (rowCount != 0) {
Class<KEY> keyClass = orMapping.getKeyClass();
if (keyClass.equals(Integer.class)) {
entity.setId((KEY) Integer.valueOf(keyHolder.getKey().intValue()));
} else if (keyClass.equals(Long.class)) {
entity.setId((KEY) Long.valueOf(keyHolder.getKey().longValue()));
}
}
return rowCount == 1 ? entity : null;
}
@Override
public Pay changeSumm(int id, double summ) {
Pay result = null;
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("id", id);
params.addValue("summ", summ);
String sqlUpdate = "update pays set summ=:summ where id=:id";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(sqlUpdate, params, keyHolder);
try {
if (keyHolder.getKey() != null) {
result = getPayById(keyHolder.getKey().intValue());
}
} catch (EmptyResultDataAccessException e) {
result = new Pay();
}
return result;
}
@Override
public GearBoxOil fillPrices(GearBoxOil oil) {
GearBoxOil result = new GearBoxOil();
GearBoxOil currentOil = getGearBoxOilByName(oil.getName());
if (currentOil.getId() > 0) {
String sqlUpdate = "update gearboxoils set price=:price where id=:id";
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("id", oil.getId());
params.addValue("price", oil.getPrice());
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(sqlUpdate, params, keyHolder);
try {
if (keyHolder.getKey() != null) {
result = getGearBoxOil(keyHolder.getKey().intValue());
}
} catch (EmptyResultDataAccessException e) {
result = new GearBoxOil();
}
}
return result;
}
@Override
public GearBoxOil createGearBoxOilWithoutPrice(GearBoxOil oil) {
GearBoxOil result = new GearBoxOil();
GearBoxOil currentOil = null;
if (oil.getId() > 0) {
currentOil =
getGearBoxOil(
oil
.getId()); // если это редактирование, в структуре уже будет Id. ТОгда
// удостоверимся, что такой элемент есть в БД
} else {
currentOil = getGearBoxOilByName(oil.getName());
}
String sqlUpdate =
"insert into gearboxoils (name, description, gearboxtype, oilstuff, manufacturer, judgement, photo"
+ ", specification, value, viscosity, discount, instock, manufacturer_code) "
+ " Values (:name, :description, :gearboxtype, :oilstuff, :manufacturer, :judgement, :photo"
+ ", :specification, :value, :viscosity, :discount, :instock, :manufacturer_code)";
MapSqlParameterSource params = new MapSqlParameterSource();
if (currentOil.getId() > 0) { // В БД есть такой элемент
sqlUpdate =
"update gearboxoils set name=:name, description=:description, gearboxtype=:gearboxtype, oilstuff=:oilstuff, judgement=:judgement"
+ ", manufacturer=:manufacturer, photo=:photo, specification=:specification, value=:value"
+ ", viscosity=:viscosity, discount=:discount, instock=:instock, manufacturer_code=:manufacturer_code where id=:id";
params.addValue("id", oil.getId());
}
params.addValue("name", oil.getName());
params.addValue("description", oil.getDescription());
params.addValue("gearboxtype", ((GearBoxType) oil.getGearBoxType()).getId());
params.addValue("oilstuff", ((OilStuff) oil.getOilStuff()).getId());
params.addValue("judgement", oil.getJudgement());
params.addValue("manufacturer", ((Manufacturer) oil.getManufacturer()).getId());
params.addValue("photo", oil.getPhoto());
params.addValue("specification", oil.getSpecification());
params.addValue("value", oil.getValue());
params.addValue("viscosity", oil.getViscosity());
params.addValue("discount", oil.getDiscount());
params.addValue("instock", oil.getInStock());
params.addValue("manufacturer_code", oil.getManufacturerCode());
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(sqlUpdate, params, keyHolder);
try {
if (keyHolder.getKey() != null) {
result = getGearBoxOil(keyHolder.getKey().intValue());
}
} catch (EmptyResultDataAccessException e) {
result = new GearBoxOil();
}
return result;
}
private long insertRegion(Region region) {
final String INSERT_SQL =
"insert into cd_region (record_key, region_type_rid, name, name_common, code_alpha, parent_region_rid, active_flag) values (?, ?, ?, ?, ?, ?, ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
connection -> {
PreparedStatement ps =
connection.prepareStatement(INSERT_SQL, new String[] {"record_id"});
ps.setString(1, UUID.randomUUID().toString());
ps.setLong(2, typeHashMap.get(region.categoryName).identity);
ps.setString(3, region.name);
ps.setString(4, getCommonName(region.code, region.getShortName()));
ps.setString(5, region.code);
if (region.parentCode == null || region.parentCode.isEmpty()) {
// No parent == region directly under country.
try {
ps.setLong(6, countryHashMap.get(region.countryCode).identity);
} catch (Exception e) {
e.printStackTrace();
}
} else {
// Subregion
ps.setLong(6, regionHashMap.get(region.parentCode).identity);
}
ps.setBoolean(7, getActiveFlag(region.code));
return ps;
},
keyHolder);
return keyHolder.getKey().longValue();
}
private long insertCountry(Country country) {
final String INSERT_SQL =
"insert into cd_region (record_key, region_type_rid, name, name_lc, name_full, name_common, code_alpha, code_alpha_alt, code_numeric) values (?, ?, ?, ?, ?, ?, ?, ?, ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
connection -> {
PreparedStatement ps =
connection.prepareStatement(INSERT_SQL, new String[] {"record_id"});
ps.setString(1, UUID.randomUUID().toString());
ps.setLong(2, 1L);
ps.setString(3, country.ShortName);
ps.setString(4, country.ShortNameLC);
ps.setString(5, country.FullName);
ps.setString(
6,
getCommonName(
country.Code2,
country
.ShortNameLC)); // populate common, to be edited after first run manually. Do
// not execute this program twice, run SQL import instead.
ps.setString(7, country.Code2);
ps.setString(8, country.Code3);
ps.setString(9, country.NumCode);
return ps;
},
keyHolder);
return keyHolder.getKey().longValue();
}
@Override
public long add(final ArtifactInfo model) {
KeyHolder holder = new GeneratedKeyHolder();
getJdbcTemplate()
.update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps =
con.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
ps.setLong(1, model.getArtifactId());
ps.setLong(2, model.getDeviceId());
ps.setLong(3, model.getUserId());
ps.setString(4, model.getCliFullFileName());
ps.setTimestamp(
5,
model.getUploadTime() == null
? null
: new Timestamp(model.getUploadTime().getTime()));
ps.setString(6, model.getSysInfo());
ps.setString(7, model.getVerInfo());
ps.setString(8, model.getAVerInfo());
return ps;
}
},
holder);
return holder.getKey().longValue();
}
public void mergeUauthors(String userid, String aid1, String aid2) throws DataAccessException {
// dont do antyhing if it's the same aid
if ((aid1 == null) || (aid2 == null) || aid1.equals(aid2)) return;
UniqueAuthor uauth1 = uauthDAO.getAuthor(aid1);
UniqueAuthor uauth2 = uauthDAO.getAuthor(aid2);
// dont do anything if one of aid is invalid...
if ((uauth1 == null) || (uauth2 == null)) return;
// start backing up
int version = getMaxVersion.run(aid1) + 1;
KeyHolder key = backupUauthor.removePapers(userid, uauth2, version, CSXConstants.USER_VERSION);
int update_id = key.getKey().intValue();
// get list of authors record affected...
List<Integer> authors = uauthDAO.getAuthorRecords(aid2);
for (Integer author_id : authors) {
backupAuthors.changeAuthors(update_id, author_id);
}
// done backing up..
// move papers to uauth1
uauthDAO.moveAuthorRecords(aid1, authors);
uauthDAO.updateAuthNdocs(aid1);
uauthDAO.updateAuthNcites(aid1);
uauthDAO.updateAuthNdocs(aid2);
}
@Override
public int save(Instruction t) {
int result = -1;
String sql =
"INSERT INTO instruction (\n"
+ " `md5`,\n"
+ " `remark`,\n"
+ " `ssid`,\n"
+ " `ver`,\n"
+ " `esn`,\n"
+ " `updatedat`,\n"
+ " `type`,\n"
+ " `url`,\n"
+ " `createdat`,\n"
+ " `num`,\n"
+ " `enable`\n"
+ ")\n"
+ "VALUES\n"
+ " (\n"
+ ":md5 , :remark ,:ssid ,:ver , :esn ,:updatedat ,:type ,:url ,:createdat ,:num ,:enable\n"
+ " )";
SqlParameterSource sps = new BeanPropertySqlParameterSource(t);
KeyHolder key = new GeneratedKeyHolder();
try {
result = this.jdbcTemplate.update(sql, sps, key);
t.setId(key.getKey().intValue());
} catch (DataAccessException e) {
logger.error(e.getMessage());
}
return result;
}
@Override
public Long findByPhone(final String phoneNumber) {
Long id;
Client client;
try {
client =
jdbcTemplate.queryForObject(
"select * from \"Client\" where phone_number = ?",
new Object[] {phoneNumber},
new ClientMapper());
id = client.getId();
} catch (DataAccessException e) {
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement ps =
connection.prepareStatement(
"INSERT INTO \"Client\" (phone_number) VALUES(?)", new String[] {"id"});
ps.setString(1, phoneNumber);
return ps;
}
},
keyHolder);
id = keyHolder.getKey().longValue();
}
return id;
}
@Override
@Loggable
public Integer addCar(Car car) throws DataAccessException {
KeyHolder keyHolder = new GeneratedKeyHolder();
namedParameterJdbcTemplate.update(insertCar, getParametersMap(car), keyHolder);
return keyHolder.getKey().intValue();
}
/**
* 更新一条数据
*
* @param forum
*/
public void addForum(final Forum forum) {
final String sql = "INSERT INTO t_forum(forum_name,forum_desc) VALUES(?,?)";
Object[] params = new Object[] {forum.getForumName(), forum.getForumDesc()};
// 方式1
// jdbcTemplate.update(sql, params);
// 方式2
// jdbcTemplate.update(sql, params,new
// int[]{Types.VARCHAR,Types.VARCHAR});
// 方式3
/*
* jdbcTemplate.update(sql, new PreparedStatementSetter() { public void
* setValues(PreparedStatement ps) throws SQLException { ps.setString(1,
* forum.getForumName()); ps.setString(2, forum.getForumDesc()); } });
*/
// 方式4
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, forum.getForumName());
ps.setString(2, forum.getForumDesc());
return ps;
}
},
keyHolder);
forum.setForumId(keyHolder.getKey().intValue());
}
public long saveFile(final FileMeta file) throws MooMoneyException {
long key = -1;
synchronized (this) {
KeyHolder keyHolder = new GeneratedKeyHolder();
getJdbcTemplate()
.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection con)
throws SQLException {
PreparedStatement statement =
con.prepareStatement(SAVE_FILE_SQL, new String[] {FILE_ID});
statement.setString(1, file.getFileName());
statement.setString(2, file.getFileNotes());
statement.setLong(3, file.getFileSize());
statement.setString(4, file.getFileType());
statement.setBytes(5, file.getBytes());
return statement;
}
},
keyHolder);
if (keyHolder != null) {
key = keyHolder.getKey().longValue();
}
}
return key;
}
@Override
public void create(Util util) {
logger.info(" #UtilDaoImpl.create # ");
final String businessCode = projectUtil.getUtilNo(util.getProjectId());
final Util finalUtil = util;
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement ps =
connection.prepareStatement(
""
+ " insert into project_util (code, detail,project_id) values (?, ?,?)"
+ "",
Statement.RETURN_GENERATED_KEYS);
ps.setString(1, businessCode);
ps.setString(2, finalUtil.getDetail());
ps.setLong(3, finalUtil.getProjectId());
return ps;
}
},
keyHolder);
Long returnid = keyHolder.getKey().longValue();
}
public Integer insert(Product product) {
Integer pk = null;
String sql =
"insert into ExProducts "
+ "(product_name, product_price, product_original_file_name, product_filesystem_name, product_content_type) "
+ "values (?, ?, ?, ?, ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
PreparedStatement pstmt = conn.prepareStatement(sql, new String[] {"product_no"});
pstmt.setString(1, product.getName());
pstmt.setInt(2, product.getPrice());
pstmt.setString(3, product.getOriginalFilename());
pstmt.setString(4, product.getFilesystemName());
pstmt.setString(5, product.getContentType());
return pstmt;
}
},
keyHolder);
pk = keyHolder.getKey().intValue();
return pk;
}
/**
* Helper method to insert an annotation and allow the other methods in this class to do the work
* of linking the annotation to the appropriate entity.
*
* @param annotationId a UUID to uniquely identify this annotation
* @param time the epoch millis at which the annotation was created
* @param timezone the timezone in which the annotation was created
* @param client the software client that generated the annotation request
* @param text the annotation text
* @return the primary key of the newly created annotation
* @throws org.springframework.dao.DataAccessException if an error occurs
*/
private long insertAnnotation(
final UUID annotationId,
final Long time,
final DateTimeZone timezone,
final String client,
final String annotationText,
final String user)
throws org.springframework.dao.DataAccessException {
final KeyHolder annotationIdKeyHolder = new GeneratedKeyHolder();
getJdbcTemplate()
.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement ps =
connection.prepareStatement(SQL_INSERT_ANNOTATION, new String[] {"id"});
ps.setString(1, annotationId.toString());
ps.setLong(2, time);
ps.setString(3, timezone.getID());
ps.setString(4, client);
ps.setString(5, annotationText);
ps.setString(6, user);
return ps;
}
},
annotationIdKeyHolder);
return annotationIdKeyHolder.getKey().longValue();
}
public long save(T entity) throws DataAccessException {
entity.update();
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new JdbcInsertStatementCreator(getSaveSQL(), getSaveValues(entity)), keyHolder);
return keyHolder.getKey().longValue();
}
/** ユーザ情報を登録する */
@Override
public int insert(PersonDto personDto) {
final StringBuilder sql = new StringBuilder();
sql.append(" INSERT INTO person");
sql.append(" (");
sql.append(" name,");
sql.append(" age,");
sql.append(" gender,");
sql.append(" birthday");
sql.append(" )");
sql.append(" VALUES");
sql.append(" (");
sql.append(" :name,");
sql.append(" :age,");
sql.append(" :gender,");
sql.append(" :birthday");
sql.append(" );");
BeanPropertySqlParameterSource paramMap = new BeanPropertySqlParameterSource(personDto);
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(sql.toString(), paramMap, keyHolder);
int insertedId = keyHolder.getKey().intValue();
return insertedId;
}
@Override
public long saveAndGetPK(final User user) {
final String INSERT_SQL =
"insert into t_user(u_username,u_password,u_email,u_sex,u_realname,u_idnum,u_tel,"
+ "u_qq,u_school,u_city,u_registertime,u_registerip,u_specialty,u_isvalidate,u_grade) "
+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
PreparedStatement ps = connection.prepareStatement(INSERT_SQL, new String[] {"id"});
ps.setString(1, user.getUserName());
ps.setString(2, user.getPassword());
ps.setString(3, user.getEmail());
ps.setString(4, user.getSex());
ps.setString(5, user.getRealName());
ps.setString(6, user.getIdNumber());
ps.setString(7, user.getTel());
ps.setString(8, user.getQq());
ps.setString(9, user.getSchool());
ps.setString(10, user.getCity());
ps.setString(11, user.getRegTime());
ps.setString(12, user.getRegIp());
ps.setString(13, user.getSpecialty());
ps.setLong(14, user.getIsvalidate());
ps.setLong(15, user.getGrade());
return ps;
}
},
keyHolder);
return Long.parseLong(keyHolder.getKey().toString());
}
@Override
public Integer addMalfunction(Malfunction malfunction) {
LOGGER.info("DAO:Add new malfunction");
KeyHolder key = new GeneratedKeyHolder();
namedParameterJdbcTemplate.update(addMalfunction, getParametersMap(malfunction), key);
return key.getKey().intValue();
}
private UploadVideoModel insert(UploadVideoModel model) {
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.getJdbcOperations().update(new UploadVideoPrepareStatement(model), keyHolder);
model.setId(keyHolder.getKey().longValue());
return model;
}
public long insertWithGeneratedKey(final String sql, final Map<String, ?> paramMap) {
MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource(paramMap);
KeyHolder keyHolder = new GeneratedKeyHolder();
int row = this.jdbcTemplate.update(sql, mapSqlParameterSource, keyHolder);
if (row > 0) return keyHolder.getKey().longValue(); // line 72
return -1;
}
@Override
public Pay createPay(Pay pay) {
Pay result = new Pay();
Pay currentPay = pay;
MapSqlParameterSource params = new MapSqlParameterSource();
String sqlUpdate =
"insert into pays (time, demand_id, manufacturer, storno, summ, user, numDoc, client)"
+ " Values (:time, :demand_id, :manufacturer, :storno, :summ, :user, :NumDoc, :client)";
if (currentPay.getId() > 0) { // В БД есть такой элемент
sqlUpdate =
"update pays set time=:time, demand_id=:demand_id, manufacturer=:manufacturer, storno=:storno"
+ ", summ=:summ, user=:user, numDoc=:NumDoc, client=:client where id=:id";
params.addValue("id", currentPay.getId());
}
params.addValue("time", pay.getTime());
params.addValue("demand_id", pay.getDemand_id());
params.addValue(
"user",
(((User) pay.getUser()).getId() == 0
? Service.ID_CUSTOMER
: ((User) pay.getUser()).getId()));
params.addValue("manufacturer", ((Manufacturer) pay.getManufacturer()).getId());
params.addValue("storno", (pay.isStorno() ? 1 : 0));
params.addValue("summ", (pay.isStorno() ? -1 : 1) * pay.getSumm());
params.addValue("NumDoc", pay.getNumDoc());
params.addValue("client", ((Client) pay.getClient()).getId());
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(sqlUpdate, params, keyHolder);
try {
if (keyHolder.getKey() != null) {
result = getPayById(keyHolder.getKey().intValue());
}
} catch (EmptyResultDataAccessException e) {
result = new Pay();
}
return result;
}
public InstPlayerCard add(final InstPlayerCard model, int instPlayerId) throws Exception {
try {
final String updateTime =
new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(System.currentTimeMillis());
StringBuilder strSql = new StringBuilder();
strSql.append(" insert into Inst_Player_Card (");
strSql.append(
"instPlayerId,cardId,qualityId,starLevelId,titleDetailId,sex,exp,level,inTeam,useTalentValue,instPlayerKungFuId,instPlayerConstells,potential,isLock,trainNum,trainAcceptNum,version,insertTime,updateTime");
strSql.append(" )");
strSql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
final String sql = strSql.toString();
KeyHolder keyHolder = new GeneratedKeyHolder();
this.getJdbcTemplate()
.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection conn)
throws SQLException {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, model.getInstPlayerId());
ps.setInt(2, model.getCardId());
ps.setInt(3, model.getQualityId());
ps.setInt(4, model.getStarLevelId());
ps.setInt(5, model.getTitleDetailId());
ps.setInt(6, model.getSex());
ps.setInt(7, model.getExp());
ps.setInt(8, model.getLevel());
ps.setInt(9, model.getInTeam());
ps.setInt(10, model.getUseTalentValue());
ps.setInt(11, model.getInstPlayerKungFuId());
ps.setString(12, model.getInstPlayerConstells());
ps.setInt(13, model.getPotential());
ps.setInt(14, model.getIsLock());
ps.setInt(15, model.getTrainNum());
ps.setInt(16, model.getTrainAcceptNum());
ps.setInt(17, 0);
ps.setString(18, updateTime);
ps.setString(19, updateTime);
return ps;
}
},
keyHolder);
model.setId(keyHolder.getKey().intValue());
model.setVersion(0);
model.setInsertTime(updateTime, 0);
model.setUpdateTime(updateTime, 0);
PlayerMemObj playerMemObj = getPlayerMemObjByPlayerId(instPlayerId);
if (instPlayerId != 0 && isUseCach() && playerMemObj != null) {
playerMemObj.instPlayerCardMap.put(model.getId(), model);
}
} catch (Exception e) {
throw e;
}
return model;
}
@Override
public int addNewOrder(Map<String, Integer> shoppingCart, double price, String username) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String date = sdf.format(new Date(System.currentTimeMillis()));
String query = "SELECT userId FROM " + userTable + " WHERE username=?";
int userId =
jdbcTemplate.queryForObject(
query, new Object[] {username}, new int[] {Types.VARCHAR}, Integer.class);
// http://stackoverflow.com/a/1668361
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps =
con.prepareStatement(
"INSERT INTO `"
+ orderTable
+ "` (customerId, date, sum) VALUES ('"
+ userId
+ "', '"
+ date
+ "', '"
+ price
+ "')",
Statement.RETURN_GENERATED_KEYS);
return ps;
}
},
keyHolder);
int invoiceNumber = keyHolder.getKey().intValue();
for (String isbn : shoppingCart.keySet()) {
jdbcTemplate.update(
new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
String query =
"INSERT INTO `"
+ orderItemTable
+ "` (invoiceNumber, isbn, quantity) VALUES (?, ?, ?)";
PreparedStatement ps = con.prepareStatement(query);
ps.setInt(1, invoiceNumber);
ps.setString(2, isbn);
ps.setInt(3, shoppingCart.get(isbn));
return ps;
}
});
}
return invoiceNumber;
}
public Integer doFunction() {
try {
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(new createParams(dataAccount), keyHolder);
return keyHolder.getKey().intValue();
} catch (Exception e) {
return null;
}
}
/* this should be conditional based on whether the id is present or not */
@Transactional
public void save(final OrderStatus orderStatus) {
if (orderStatus.getId() == 0) {
KeyHolder keyHolder = new GeneratedKeyHolder();
int update =
jdbcTemplate.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection connection)
throws SQLException {
if (orderStatus.getCreatedTime() == null) {
orderStatus.setCreatedTime(new Date());
}
java.sql.Timestamp createdTime =
new java.sql.Timestamp(orderStatus.getCreatedTime().getTime());
if (orderStatus.getUpdatedTime() == null) {
orderStatus.setUpdatedTime(new Date());
}
java.sql.Timestamp updatedTime =
new java.sql.Timestamp(orderStatus.getUpdatedTime().getTime());
PreparedStatement ps =
connection.prepareStatement(INSERT_SQL, new String[] {"id"});
ps.setTimestamp(1, createdTime);
ps.setTimestamp(2, updatedTime);
ps.setInt(3, orderStatus.getOrderId());
ps.setInt(4, orderStatus.getStatusId());
return ps;
}
},
keyHolder);
Number unId = keyHolder.getKey();
orderStatus.setId(unId.intValue());
} else {
String sql =
"UPDATE order_status set updated_time = ? ,order_id = ? ,status_id = ? where id = ? ";
jdbcTemplate.update(
sql,
new Object[] {
orderStatus.getUpdatedTime(),
orderStatus.getOrderId(),
orderStatus.getStatusId(),
orderStatus.getId()
});
}
}
@Override
public void save(SysSensitiveWord sysSensitiveWord) {
// 构造SQL的参数
Map<String, Object> paramMap = toMap(sysSensitiveWord);
KeyHolder keyHolder = new GeneratedKeyHolder();
new NamedParameterJdbcTemplate(jdbcTemplate)
.update(INSERT_SQL, new MapSqlParameterSource(paramMap), keyHolder);
// 生成主键
sysSensitiveWord.setWordId(keyHolder.getKey().longValue());
}
public DictBeautyCardExp add(final DictBeautyCardExp model, int instPlayerId) throws Exception {
try {
StringBuilder strSql = new StringBuilder();
strSql.append(" insert into Dict_Beauty_CardExp (");
strSql.append(
"exp,veryLow,veryLowPr,veryLowPrAdd,low,lowPr,lowPrAdd,centre,centrePr,centrePrAdd,tall,tallPr,tallPrAdd,description,version");
strSql.append(" )");
strSql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
final String sql = strSql.toString();
KeyHolder keyHolder = new GeneratedKeyHolder();
this.getJdbcTemplate()
.update(
new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection conn)
throws SQLException {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, model.getExp());
ps.setInt(2, model.getVeryLow());
ps.setFloat(3, model.getVeryLowPr());
ps.setFloat(4, model.getVeryLowPrAdd());
ps.setFloat(5, model.getLow());
ps.setFloat(6, model.getLowPr());
ps.setFloat(7, model.getLowPrAdd());
ps.setFloat(8, model.getCentre());
ps.setFloat(9, model.getCentrePr());
ps.setFloat(10, model.getCentrePrAdd());
ps.setFloat(11, model.getTall());
ps.setFloat(12, model.getTallPr());
ps.setFloat(13, model.getTallPrAdd());
ps.setString(14, model.getDescription());
ps.setInt(15, 0);
return ps;
}
},
keyHolder);
model.setId(keyHolder.getKey().intValue());
model.setVersion(0);
PlayerMemObj playerMemObj = getPlayerMemObjByPlayerId(instPlayerId);
if (instPlayerId != 0 && isUseCach() && playerMemObj != null) {
playerMemObj.dictBeautyCardExpMap.put(model.getId(), model);
}
} catch (Exception e) {
throw e;
}
return model;
}