@Override
public long save(MisoPrintService printService) throws IOException {
MapSqlParameterSource params = new MapSqlParameterSource();
params
.addValue("serviceName", printService.getName())
.addValue("contextName", printService.getPrintContext().getName())
.addValue("enabled", printService.isEnabled())
.addValue("printServiceFor", printService.getPrintServiceFor().getName());
try {
JSONObject contextFields =
PrintServiceUtils.mapContextFieldsToJSON(printService.getPrintContext());
String contextFieldJSON = contextFields.toString();
params.addValue("contextFields", contextFieldJSON);
} catch (IllegalAccessException e) {
e.printStackTrace();
}
if (printService.getServiceId() == -1) {
SimpleJdbcInsert insert =
new SimpleJdbcInsert(template)
.withTableName(TABLE_NAME)
.usingGeneratedKeyColumns("serviceId");
Number newId = insert.executeAndReturnKey(params);
printService.setServiceId(newId.longValue());
} else {
params.addValue("serviceId", printService.getServiceId());
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
namedTemplate.update(PRINT_SERVICE_UPDATE, params);
}
return printService.getServiceId();
}
private String saveTxn(TransactionVO currentTransaction) throws ParseException {
insertTransaction =
new SimpleJdbcInsert(getDataSource())
.withTableName("transaction")
.usingGeneratedKeyColumns("id");
SqlParameterSource parameters =
new MapSqlParameterSource()
.addValue("dateReported", getMySQLSaveDate(currentTransaction.getDateReported()))
.addValue("customerId", currentTransaction.getCustomerId())
.addValue("productCategory", currentTransaction.getProductCategory())
.addValue("makeId", currentTransaction.getMakeId())
.addValue("modelId", currentTransaction.getModelId())
.addValue("serialNo", currentTransaction.getSerialNo())
.addValue("accessories", currentTransaction.getAccessories())
.addValue("complaintReported", currentTransaction.getComplaintReported())
.addValue("complaintDiagnosed", currentTransaction.getComplaintDiagonsed())
.addValue("engineerRemarks", currentTransaction.getEnggRemark())
.addValue("repairAction", currentTransaction.getRepairAction())
.addValue("note", currentTransaction.getNotes())
.addValue("status", currentTransaction.getStatus())
.addValue("createdOn", currentTransaction.getCreatedOn())
.addValue("modifiedOn", currentTransaction.getModifiedOn())
.addValue("createdBy", currentTransaction.getCreatedBy())
.addValue("modifiedBy", currentTransaction.getModifiedBy());
Number newId = insertTransaction.executeAndReturnKey(parameters);
LOG.info(" the queryForInt resulted in " + newId.longValue());
currentTransaction.setId(newId.longValue());
// update the tag No
String tagNo = "WON2N" + newId.longValue();
String query = "update transaction set TagNo = '" + tagNo + "' where id =" + newId.longValue();
getJdbcTemplate().update(query);
return tagNo;
}
@Override
public Ride addRide(Ride ride) throws Exception {
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("status", ride.getStatus());
parameters.put("origin_lat", ride.getOriginLat());
parameters.put("origin_long", ride.getOriginLong());
parameters.put("dest_lat", ride.getDestLat());
parameters.put("dest_long", ride.getDestLong());
parameters.put("start_datetime", ride.getStartDatetime());
parameters.put("end_datetime", ride.getEndDatetime());
parameters.put("fare", ride.getFare());
parameters.put("rider_rate_quality", ride.getRiderRateQuality());
parameters.put("rider_rate_msg", ride.getRiderRateMsg());
parameters.put("driver_rate_quality", ride.getDriverRateQuality());
parameters.put("driver_rate_msg", ride.getDriverRateMsg());
parameters.put("riders_rider_id", ride.getRidersRiderId());
parameters.put("drivers_driver_id", ride.getDriversDriverId());
SimpleJdbcInsert insert =
new SimpleJdbcInsert(ds).withTableName("rides").usingGeneratedKeyColumns("ride_id");
Number newId = insert.executeAndReturnKey(parameters);
ride.setRideId(newId.intValue());
return ride;
}
/*
* (non-Javadoc)
*
* @see org.cuny.sensors.dao.IDao#save(java.lang.Object)
*/
@Override
public RawFile save(RawFile obj) throws Exception {
Map<String, Object> parameters = new HashMap<String, Object>(5);
parameters.put(FILE_NAME, obj.getFilename());
parameters.put(FILE_SIZE, obj.getFileSize());
parameters.put(STATUS, obj.getStatus().toString());
parameters.put(BUILDING, obj.getBuilding());
parameters.put(CAMPUS, obj.getCampus());
Number newId = insertActor.executeAndReturnKey(parameters);
obj.setId(newId.longValue());
return obj;
}
public long addAndReturnPrimaryKey(Object object) {
if (primaryKeyName == null || "".equals(primaryKeyName)) {
throw new IllegalStateException(
"需要使用addAndReturnPrimaryKey方法,必须在dao的@Table注解设置primaryKeyName的值");
}
SqlParameterSource sps = new BeanPropertySqlParameterSource(object);
if (log.isDebugEnabled()) log.debug("准备插入对象：" + object);
@SuppressWarnings("unchecked")
long id = simpleJdbcInsert.executeAndReturnKey(sps).longValue();
if (log.isDebugEnabled()) log.debug("插入成功:" + object + "\t" + "primary key为:" + id);
return id;
}
private Vehicle insert(Vehicle vehicle) {
SimpleJdbcInsert insert =
new SimpleJdbcInsert(template).withTableName("vehicles").usingGeneratedKeyColumns("id");
SqlParameterSource parameters =
new MapSqlParameterSource()
.addValue("manufacturer", vehicle.getManufacturer())
.addValue("model", vehicle.getModel());
Number id = insert.executeAndReturnKey(parameters);
vehicle.setId(id.intValue());
return vehicle;
}
@AuditTrail
public Invoice create(final Invoice invoice) {
SimpleJdbcInsert insert =
new SimpleJdbcInsert(dataSource).withTableName("invoice").usingGeneratedKeyColumns("id");
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("account_id", invoice.getAccountId().intValue());
parameters.put("period_from", invoice.getPeriodFrom());
parameters.put("period_to", invoice.getPeriodTo());
parameters.put("sent_time", invoice.getSentTime());
parameters.put("state", invoice.getState());
LOG.debug(parameters.toString());
Number id = insert.executeAndReturnKey(parameters);
invoice.setId(BigInteger.valueOf(id.longValue()));
return invoice;
}
public int saveUser(User user) throws RuntimeException {
logger.info("--> cs.simple.console.service.impl.UserServiceImpl.saveUser");
int generateKey = 0;
try {
user.setDeleted(Constant.N);
user.setPassword(password(user.getPassword()));
// password
generateKey =
jdbcInsert.executeAndReturnKey(new BeanPropertySqlParameterSource(user)).intValue();
} catch (Exception e) {
logger.error(ExceptionUtils.getStackTrace(e));
throw new RuntimeException(e);
}
logger.info("<-- cs.simple.console.service.impl.UserServiceImpl.saveUser");
return generateKey;
}
public void save(Deafness deafness) {
Map<String, Object> geneticsMap = new HashMap<String, Object>();
geneticsMap.put(ID_FIELD_NAME, deafness.getId());
geneticsMap.put(RADAR_NO_FIELD_NAME, deafness.getRadarNo());
geneticsMap.put(EVIDENCE_OF_DEAFNESS_FIELD_NAME, deafness.getEvidenceOfDeafness().getId());
geneticsMap.put(DATE_PROBLEM_FIRST_NOTICED_FIELD_NAME, deafness.getDateProblemFirstNoticed());
geneticsMap.put(
DATE_STARTED_USING_HEARING_AID_FIELD_NAME, deafness.getDateStartedUsingHearingAid());
if (deafness.hasValidId()) {
namedParameterJdbcTemplate.update(
buildUpdateQuery(TABLE_NAME, ID_FIELD_NAME, geneticsMap), geneticsMap);
} else {
Number id = deafnessInsert.executeAndReturnKey(geneticsMap);
deafness.setId(id.longValue());
}
}
public long saveGroup(Group group) throws IOException {
MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("name", group.getName()).addValue("description", group.getDescription());
if (group.getGroupId() == Group.UNSAVED_ID) {
SimpleJdbcInsert insert =
new SimpleJdbcInsert(template)
.withTableName("_Group")
.usingGeneratedKeyColumns("groupId");
Number newId = insert.executeAndReturnKey(params);
group.setGroupId(newId.longValue());
} else {
params.addValue("groupId", group.getGroupId());
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
namedTemplate.update(GROUP_UPDATE, params);
}
return group.getGroupId();
}
public synchronized CookingRecipe insertCookingRecipe(CookingRecipe recipe) throws Exception {
System.out.println("Database: insertCookingRecipe(" + recipe + ")");
SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate);
insert.withTableName("recipes");
insert.setGeneratedKeyName("id");
Map<String, Object> mapa = new HashMap<String, Object>();
mapa.put("recipe_name", recipe.getName());
mapa.put("author", recipe.getAuthor());
mapa.put("guide", recipe.getGuide());
// mapa.put("incident_ID", incidentID);
Number insertedRecipeId = insert.executeAndReturnKey(mapa);
recipe.setId(insertedRecipeId.longValue());
insertIngredients(recipe.getIngredients(), insertedRecipeId.longValue());
for (CookingIngredient ci : recipe.getIngredients()) {
ci.setRecipe_id(insertedRecipeId.longValue());
}
return recipe;
}
public void save(Genetics genetics) {
Map<String, Object> geneticsMap = new HashMap<String, Object>();
geneticsMap.put(ID_FIELD_NAME, genetics.getId());
geneticsMap.put(RADAR_NO_FIELD_NAME, genetics.getRadarNo());
if (genetics.getTestsDone() != null) {
geneticsMap.put(TESTS_DONE_FIELD_NAME, genetics.getTestsDone().getId());
}
geneticsMap.put(LAB_WHERE_TESTS_WERE_DONE_FIELD_NAME, genetics.getLabWhereTestWasDone());
geneticsMap.put(TESTS_DONE_ON_FIELD_NAME, genetics.getTestDoneOn());
geneticsMap.put(REFERENCE_NUMBER_FIELD_NAME, genetics.getReferenceNumber());
geneticsMap.put(WHAT_RESULTS_SHOWED_FIELD_NAME, genetics.getWhatResultsShowed());
geneticsMap.put(KEY_EVIDENCE_FIELD_NAME, genetics.getKeyEvidence());
geneticsMap.put(DATE_SENT_FIELD_NAME, genetics.getDateSent());
if (genetics.hasValidId()) {
namedParameterJdbcTemplate.update(
buildUpdateQuery(TABLE_NAME, ID_FIELD_NAME, geneticsMap), geneticsMap);
} else {
Number id = geneticsInsert.executeAndReturnKey(geneticsMap);
genetics.setId(id.longValue());
}
}
@Override
public User save(User user) {
MapSqlParameterSource map =
new MapSqlParameterSource()
.addValue("id", user.getId())
.addValue("name", user.getName())
.addValue("email", user.getEmail())
.addValue("password", user.getPassword())
.addValue("registered", user.getRegistered())
.addValue("enabled", user.isEnabled())
.addValue("caloriesPerDay", user.getCaloriesPerDay());
if (user.isNew()) {
Number newKey = insertUser.executeAndReturnKey(map);
user.setId(newKey.intValue());
} else {
namedParameterJdbcTemplate.update(
"UPDATE users SET name=:name, email=:email, password=:password, "
+ "registered=:registered, enabled=:enabled, calories_per_day=:caloriesPerDay WHERE id=:id",
map);
}
return user;
}
public long save(Platform platform) throws IOException {
// execute this procedure...
MapSqlParameterSource params = new MapSqlParameterSource();
params
.addValue("name", platform.getPlatformType().getKey())
.addValue("instrumentModel", platform.getInstrumentModel())
.addValue("description", platform.getDescription())
.addValue("numContainers", platform.getNumContainers());
if (platform.getPlatformId() == null) {
SimpleJdbcInsert insert =
new SimpleJdbcInsert(template)
.withTableName(TABLE_NAME)
.usingGeneratedKeyColumns("platformId");
Number newId = insert.executeAndReturnKey(params);
platform.setPlatformId(newId.longValue());
} else {
params.addValue("platformId", platform.getPlatformId());
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
namedTemplate.update(PLATFORM_UPDATE, params);
}
return platform.getPlatformId();
}
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
LOG.info("Set up initial values");
LOG.debug("Insert default billing plan");
SimpleJdbcInsert billingJdbcInsert =
new SimpleJdbcInsert(jdbcTemplate)
.withTableName("s_billing_plan")
.usingColumns(
"billingType",
"numUsers",
"volume",
"numProjects",
"pricing",
"hasBugEnable",
"hasStandupMeetingEnable",
"hasTimeTracking")
.usingGeneratedKeyColumns("id");
Map<String, Object> billingParameters = new HashMap<>();
billingParameters.put("billingType", "Community");
billingParameters.put("numUsers", 99999999);
billingParameters.put("volume", 999999999999L);
billingParameters.put("numProjects", 999999);
billingParameters.put("pricing", 0);
billingParameters.put("hasBugEnable", Boolean.TRUE);
billingParameters.put("hasStandupMeetingEnable", Boolean.TRUE);
billingParameters.put("hasTimeTracking", Boolean.TRUE);
Number billingPlanId = billingJdbcInsert.executeAndReturnKey(billingParameters);
LOG.debug("Insert default account");
SimpleJdbcInsert accountJdbcInsert =
new SimpleJdbcInsert(jdbcTemplate)
.withTableName("s_account")
.usingColumns("status", "billingPlanId", "paymentMethod", "subdomain")
.usingGeneratedKeyColumns("id");
Map<String, Object> accountParameters = new HashMap<>();
accountParameters.put("status", "Active");
accountParameters.put("billingPlanId", billingPlanId);
accountParameters.put("paymentMethod", "None");
accountParameters.put("subdomain", "");
Number accountId = accountJdbcInsert.executeAndReturnKey(accountParameters);
LOG.debug("Insert default users");
SimpleJdbcInsert userJdbcInsert =
new SimpleJdbcInsert(jdbcTemplate)
.withTableName("s_user")
.usingColumns(
"username",
"firstname",
"lastname",
"email",
"status",
"registeredTime",
"password",
"timezone");
Date nowDate = DateTimeUtils.convertDateTimeToUTC(new GregorianCalendar().getTime());
String timezoneDbId = TimezoneMapper.getTimezoneDbId(TimeZone.getDefault());
Map<String, Object> userParameters = new HashMap<>();
userParameters.put("username", "*****@*****.**");
userParameters.put("firstname", "");
userParameters.put("lastname", "admin");
userParameters.put("email", "*****@*****.**");
userParameters.put("status", "Active");
userParameters.put("registeredTime", nowDate);
userParameters.put("password", PasswordEncryptHelper.encryptSaltPassword("admin123"));
userParameters.put("timezone", timezoneDbId);
userJdbcInsert.execute(userParameters);
LOG.debug("Insert default user avatar");
LOG.debug("Create associate between user and billing plan");
SimpleJdbcInsert userAccountJdbcInsert =
new SimpleJdbcInsert(jdbcTemplate)
.withTableName("s_user_account")
.usingColumns(
"username", "accountId", "isAccountOwner", "registeredTime", "registerStatus")
.usingGeneratedKeyColumns("id");
Map<String, Object> userAccountParameters = new HashMap<>();
userAccountParameters.put("username", "*****@*****.**");
userAccountParameters.put("accountId", accountId);
userAccountParameters.put("isAccountOwner", Boolean.TRUE);
userAccountParameters.put("registeredTime", nowDate);
userAccountParameters.put("registerStatus", "Active");
userAccountJdbcInsert.executeAndReturnKey(userAccountParameters);
LOG.debug("Insert default roles");
SimpleJdbcInsert roleJdbcInsert =
new SimpleJdbcInsert(jdbcTemplate)
.withTableName("s_roles")
.usingColumns("rolename", "description", "sAccountId", "isSystemRole")
.usingGeneratedKeyColumns("id");
LOG.debug("Create default admin role");
SqlParameterSource adminRoleParameters =
new MapSqlParameterSource()
.addValue("rolename", "Administrator")
.addValue("description", "Admin Role")
.addValue("sAccountId", accountId)
.addValue("isSystemRole", Boolean.TRUE);
Number adminRoleId = roleJdbcInsert.executeAndReturnKey(adminRoleParameters);
LOG.debug("Create default employee role");
SqlParameterSource employeeRoleParameters =
new MapSqlParameterSource()
.addValue("rolename", "Employee")
.addValue("description", "Employee Role")
.addValue("sAccountId", accountId)
.addValue("isSystemRole", Boolean.TRUE);
Number employeeRoleId = roleJdbcInsert.executeAndReturnKey(employeeRoleParameters);
LOG.debug("Create default guest role");
SqlParameterSource guestRoleParameters =
new MapSqlParameterSource()
.addValue("rolename", "Guest")
.addValue("description", "Guest Role")
.addValue("sAccountId", accountId)
.addValue("isSystemRole", Boolean.TRUE);
Number guestRoleId = roleJdbcInsert.executeAndReturnKey(guestRoleParameters);
LOG.debug("Associate permission with admin role");
SimpleJdbcInsert rolePermissionJdbcInsert =
new SimpleJdbcInsert(jdbcTemplate)
.withTableName("s_role_permission")
.usingColumns("roleid", "roleVal")
.usingGeneratedKeyColumns("id");
SqlParameterSource adminRolePermissionParameters =
new MapSqlParameterSource()
.addValue("roleid", adminRoleId)
.addValue("roleVal", PermissionMap.buildAdminPermissionCollection().toJsonString());
rolePermissionJdbcInsert.execute(adminRolePermissionParameters);
LOG.debug("Associate permission with employee role");
SqlParameterSource employeeRolePermissionParameters =
new MapSqlParameterSource()
.addValue("roleid", employeeRoleId)
.addValue("roleVal", PermissionMap.buildEmployeePermissionCollection().toJsonString());
rolePermissionJdbcInsert.execute(employeeRolePermissionParameters);
LOG.debug("Associate permission with guest role");
SqlParameterSource guestRolePermissionParameters =
new MapSqlParameterSource()
.addValue("roleid", guestRoleId)
.addValue("roleVal", PermissionMap.buildGuestPermissionCollection().toJsonString());
rolePermissionJdbcInsert.execute(guestRolePermissionParameters);
}
@Transactional(readOnly = false, rollbackFor = IOException.class)
@TriggersRemove(
cacheName = {"userCache", "lazyUserCache"},
keyGenerator =
@KeyGenerator(
name = "HashCodeCacheKeyGenerator",
properties = {
@Property(name = "includeMethod", value = "false"),
@Property(name = "includeParameterTypes", value = "false")
}))
public long saveUser(User user) throws IOException {
Blob roleBlob = null;
if (user.getRoles() != null) {
List<String> roles = new ArrayList<String>(Arrays.asList(user.getRoles()));
if (user.isExternal() && !roles.contains("ROLE_EXTERNAL")) roles.add("ROLE_EXTERNAL");
if (user.isInternal() && !roles.contains("ROLE_INTERNAL")) roles.add("ROLE_INTERNAL");
if (user.isAdmin() && !roles.contains("ROLE_ADMIN")) roles.add("ROLE_ADMIN");
user.setRoles(roles.toArray(new String[user.getRoles().length]));
try {
if (user.getRoles().length > 0) {
byte[] rbytes = LimsUtils.join(user.getRoles(), ",").getBytes();
roleBlob = new SerialBlob(rbytes);
}
} catch (SerialException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
MapSqlParameterSource params = new MapSqlParameterSource();
params
.addValue("active", user.isActive())
.addValue("admin", user.isAdmin())
.addValue("external", user.isExternal())
.addValue("fullName", user.getFullName())
.addValue("internal", user.isInternal())
.addValue("loginName", user.getLoginName())
.addValue("roles", roleBlob)
.addValue("email", user.getEmail());
if (passwordCodecService != null) {
params.addValue("password", passwordCodecService.encrypt(user.getPassword()));
} else {
log.warn(
"No PasswordCodecService has been wired to this SQLSecurityDAO. This means your passwords may be being "
+ "stored in plaintext, if not already encrypted. Please specify a PasswordCodecService in your Spring config and (auto)wire it "
+ "to this DAO.");
params.addValue("password", user.getPassword());
}
if (user.getUserId() == UserImpl.UNSAVED_ID) {
SimpleJdbcInsert insert =
new SimpleJdbcInsert(template).withTableName("User").usingGeneratedKeyColumns("userId");
Number newId = insert.executeAndReturnKey(params);
user.setUserId(newId.longValue());
} else {
params.addValue("userId", user.getUserId());
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
namedTemplate.update(USER_UPDATE, params);
}
// sort User_Group
// delete existing joins
MapSqlParameterSource delparams = new MapSqlParameterSource();
delparams.addValue("userId", user.getUserId());
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
namedTemplate.update(USER_GROUP_DELETE_BY_USER_ID, delparams);
if (user.getGroups() != null && !user.getGroups().isEmpty()) {
SimpleJdbcInsert eInsert = new SimpleJdbcInsert(template).withTableName("User_Group");
for (Group g : user.getGroups()) {
MapSqlParameterSource ugParams = new MapSqlParameterSource();
ugParams
.addValue("users_userId", user.getUserId())
.addValue("groups_groupId", g.getGroupId());
eInsert.execute(ugParams);
}
}
return user.getUserId();
}
