private void uploadFlow(
Connection connection, Project project, int version, Flow flow, EncodingType encType)
throws ProjectManagerException, IOException {
QueryRunner runner = new QueryRunner();
String json = JSONUtils.toJSON(flow.toObject());
byte[] stringData = json.getBytes("UTF-8");
byte[] data = stringData;
logger.info("UTF-8 size:" + data.length);
if (encType == EncodingType.GZIP) {
data = GZIPUtils.gzipBytes(stringData);
}
logger.info("Flow upload " + flow.getId() + " is byte size " + data.length);
final String INSERT_FLOW =
"INSERT INTO project_flows (project_id, version, flow_id, modified_time, encoding_type, json) values (?,?,?,?,?,?)";
try {
runner.update(
connection,
INSERT_FLOW,
project.getId(),
version,
flow.getId(),
System.currentTimeMillis(),
encType.getNumVal(),
data);
} catch (SQLException e) {
throw new ProjectManagerException("Error inserting flow " + flow.getId(), e);
}
}
public void insertOrUpdate(User user) throws SQLException {
String sql = "";
if (select(user.getId()) != null) {
sql = "update user set name=?, email=?,password=?,gender=?,role=?,age=? where id = ?";
queryRunner.update(
sql,
user.getIdNumber(),
user.getUserName(),
user.getEmail(),
user.getPassword(),
user.getGender(),
user.getRole(),
user.getAge(),
user.getId());
} else {
sql = "insert into user(idNumber,name,email,password,gender,role,age) values(?,?,?,?,?,?,?)";
queryRunner.update(
sql,
user.getIdNumber(),
user.getUserName(),
user.getEmail(),
user.getPassword(),
user.getGender(),
user.getRole(),
user.getAge());
}
System.out.println("Insert Or Update:" + sql);
}
@BeforeClass
public void setup() throws Exception {
System.setProperty(LensConfConstants.CONFIG_LOCATION, "target/test-classes/");
Configuration conf = LensServerConf.getHiveConf();
QueryRunner runner = new QueryRunner(UtilityMethods.getDataSourceFromConfForScheduler(conf));
// Cleanup all tables
runner.update("DROP TABLE IF EXISTS job_table");
runner.update("DROP TABLE IF EXISTS job_instance_table");
this.schedulerDAO = new SchedulerDAO(conf);
}
/**
* 执行INSERT/UPDATE/DELETE语句
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public int excute(String sql, Object... params) throws SQLException {
printIn(sql);
printIn("params length:" + params.length);
for (int i = 0; i < params.length; i++) {
printIn("params" + i + ":" + params[i]);
}
if (sql.toLowerCase().indexOf("insert") == -1) {
return queryRunner.update(getConnection(), sql, params);
} else {
return queryRunner.update(getConnection(), sql, params);
}
}
/** {@inheritDoc} */
@Override
public synchronized void setAnalysisDataForFile(
String filePath,
String repository,
AstNode binaryIndex,
List<Usage> usages,
List<String> types,
List<String> imports)
throws DatabaseAccessException {
int fileId = ensureThatRecordExists(filePath, repository);
int repoId = getRepoIdForRepoName(repository);
QueryRunner run = new QueryRunner(dataSource);
try {
// Clears all types set for the file in a previous indexing process
run.update(STMT_CLEAR_TYPES_FOR_FILE, fileId);
// Clears all imports set for the file in a previous indexing procedure
run.update(STMT_CLEAR_IMPORTS_FOR_FILE, fileId);
// Sets the Binary index and the usages for the file
run.update(STMT_SET_BINARY_INDEX_AND_USAGES_FOR_FILE, binaryIndex, usages, fileId);
// Sets the types for the file, if any
if (!(types.isEmpty())) {
// extends the insert into statement by one line for each type in the list of types
StringBuilder statement = new StringBuilder(STMT_CREATE_TYPES_FOR_FILE);
String[] params = new String[types.size()];
for (int i = 0; i < types.size(); i++) {
statement.append("(?, ").append(fileId).append(", ").append(repoId).append("),");
params[i] = types.get(i);
}
statement.deleteCharAt(statement.length() - 1);
run.update(statement.toString(), (Object[]) params);
}
// Sets the imports for the file, if any
if (!imports.isEmpty()) {
// first build the import statement by adding all values
StringBuilder importString = new StringBuilder(STMT_SET_IMPORTS_FOR_FILE);
String[] params = new String[imports.size()];
for (int i = 0; i < imports.size(); i++) {
importString.append("(").append(fileId).append(", ?),");
params[i] = imports.get(i);
}
importString.deleteCharAt(importString.length() - 1);
run.update(importString.toString(), (Object[]) params);
}
} catch (SQLException ex) {
throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex);
}
}
@Override
public void cancerFocusUser(long userId, long focusUserId) throws SQLException {
QueryRunner queryRunner = new QueryRunner(DbTools.getDatasource());
String sql = "delete from focus where user_id=? and Use_user_id=? ";
Object[] param = {userId, focusUserId};
queryRunner.update(sql, param);
}
/**
* 删除指定个Id的数据
*
* @param id
* @return
*/
public boolean deleteById(Serializable id, Class classz) {
DBTableMeta dbTableMeta = DBTableMetaFactory.getDBTableMeta(classz);
String sql = getSqlAnalyzer().analyzeDeleteByPrimaryKey(dbTableMeta);
printSQL(sql, new Object[] {id});
QueryRunner queryRunner = new QueryRunner();
Connection conn = getDbConnectionFactory().getConnection();
try {
int num = queryRunner.update(conn, sql, id);
if (num == 1) {
return true;
} else if (num == 0) {
return false;
} else {
throw new SQLException("数据删除异常,错误的sql与参数导致删除了" + num + "条数据");
}
} catch (SQLException e) {
throw new RuntimeException("删除数据失败", e);
} finally {
getDbConnectionFactory().releaseConnection(conn);
}
}
@Override
public void add(UserIdMap userIdMap) {
Connection conn = null;
Object[] params = null;
String sql = null;
QueryRunner queryRunner = new QueryRunner();
try {
conn = Conn.get();
sql = "select * from user_id_map where passengerId=? and clientId=? and channelId=?";
params = new Object[3];
params[0] = userIdMap.getPassengerId();
params[1] = userIdMap.getClientId();
params[2] = userIdMap.getChannelId();
UserIdMap oldMap =
queryRunner.query(conn, sql, new BeanHandler<UserIdMap>(UserIdMap.class), params);
if (oldMap == null) {
sql = "insert into user_id_map(localId,passengerId,clientId,channelId) values(?,?,?,?)";
params = new Object[4];
params[0] = IdGenerator.seq();
params[1] = userIdMap.getPassengerId();
params[2] = userIdMap.getClientId();
params[3] = userIdMap.getChannelId();
queryRunner.update(conn, sql, params);
}
} catch (SQLException e) {
logger.info(e.getMessage(), e);
}
}
private void updateProjectSettings(Connection connection, Project project, EncodingType encType)
throws ProjectManagerException {
QueryRunner runner = new QueryRunner();
final String UPDATE_PROJECT_SETTINGS =
"UPDATE projects SET enc_type=?, settings_blob=? WHERE id=?";
String json = JSONUtils.toJSON(project.toObject());
byte[] data = null;
try {
byte[] stringData = json.getBytes("UTF-8");
data = stringData;
if (encType == EncodingType.GZIP) {
data = GZIPUtils.gzipBytes(stringData);
}
logger.debug(
"NumChars: " + json.length() + " UTF-8:" + stringData.length + " Gzip:" + data.length);
} catch (IOException e) {
throw new ProjectManagerException("Failed to encode. ", e);
}
try {
runner.update(
connection, UPDATE_PROJECT_SETTINGS, encType.getNumVal(), data, project.getId());
connection.commit();
} catch (SQLException e) {
throw new ProjectManagerException(
"Error updating project " + project.getName() + " version " + project.getVersion(), e);
}
}
/**
* Saves the query passed
*
* @param savedQuery
* @return insert id
* @throws LensException
*/
public long saveQuery(SavedQuery savedQuery) throws LensException {
try {
final ECMAEscapedSavedQuery ecmaEscaped = ECMAEscapedSavedQuery.getFor(savedQuery);
runner.update(
"insert into "
+ SAVED_QUERY_TABLE_NAME
+ " values ("
+ dialect.getAutoIncrementId(runner)
+ ", "
+ "'"
+ ecmaEscaped.getName()
+ "'"
+ ", "
+ "'"
+ ecmaEscaped.getDescription()
+ "'"
+ ","
+ "'"
+ ecmaEscaped.getQuery()
+ "'"
+ ","
+ "'"
+ ecmaEscaped.getParameters()
+ "'"
+ ","
+ "now()"
+ ","
+ "now()"
+ ")");
return dialect.getLastInsertedID(runner);
} catch (SQLException e) {
throw new LensException("Save query failed !", e);
}
}
/**
* Creates the saved query table
*
* @throws LensException cannot create saved query table
*/
public void createSavedQueryTableIfNotExists() throws LensException {
try {
runner.update(dialect.getCreateTableSyntax());
} catch (SQLException e) {
throw new LensException("Cannot create saved query table!", e);
}
}
@Override // 在执行关注之前先判断用户是否已被关注
public void addFocusUser(long userId, long focusUserId) throws SQLException {
QueryRunner queryRunner = new QueryRunner(DbTools.getDatasource());
String sql = "insert into focus(user_id,Use_user_id) values(?,?)";
Object[] param = {userId, focusUserId};
queryRunner.update(sql, param);
}
public void updateState(String uid, boolean state) {
String sql = "update user set state=? where uid=?";
try {
qr.update(sql, state, uid);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
// 给某个角色分配权限
public void updatePrivilege(Role role, List<Privilege> privileges) {
try {
// 先删除角色拥有权限
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "delete from role_privilege where role_id=?";
qr.update(sql, role.getId());
// 分配新的权限
for (Privilege p : privileges) {
sql = "insert into role_privilege(role_id,privilege_id) values(?,?)";
Object params[] = {role.getId(), p.getId()};
qr.update(sql, params);
}
} catch (Exception e) {
throw new DaoException(e);
}
}
/** {@inheritDoc} */
@Override
public synchronized void deleteRepository(String repoName) throws DatabaseAccessException {
QueryRunner run = new QueryRunner(dataSource);
try {
run.update(STMT_DELETE_REPOSITORY, repoName);
} catch (SQLException ex) {
throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex);
}
}
/** {@inheritDoc} */
@Override
public synchronized void purgeDatabaseEntries() throws DatabaseAccessException {
QueryRunner run = new QueryRunner(dataSource);
try {
run.update(STMT_PURGE_ALL_RECORDS);
} catch (SQLException ex) {
throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex);
}
}
/** Delete all the register older than 30 days. */
public static void fullClear() {
QueryRunner run = new QueryRunner(DbConfigList.getDataSourceById("file"));
try {
String sql = "DELETE FROM QUEUE WHERE ADDDATE(MSG_TIME, 30) < CURDATE()";
run.update(sql);
} catch (SQLException e) {
log.fatal("unable to clear queue", e);
}
}
/**
* 删除一个用户
*
* @param id
*/
public void deleteOne(String id) {
QueryRunner queryRunner = new QueryRunner(JdbcUtils.getDataSource());
String sql = "DELETE FROM user WHERE id = ?";
try {
int update = queryRunner.update(sql, new Object[] {id});
} catch (SQLException e) {
e.printStackTrace();
}
}
public void add(Role role) {
try {
QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
String sql = "insert into role(id,name) values(?,?)";
Object params[] = {role.getId(), role.getName()};
qr.update(sql, params);
} catch (Exception e) {
throw new DaoException(e);
}
}
/** {@inheritDoc} */
@Override
public synchronized void setLastAnalyzedRevisionOfRepository(
String repositoryName, String revision) throws DatabaseAccessException {
QueryRunner run = new QueryRunner(dataSource);
try {
run.update(STMT_SET_LAST_ANALYZED_REVISION_OF_REPOSITORY, revision, repositoryName);
} catch (SQLException ex) {
throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex);
}
}
/** {@inheritDoc} */
@Override
public synchronized void deleteFile(String filePath, String repository)
throws DatabaseAccessException {
QueryRunner run = new QueryRunner(dataSource);
try {
run.update(STMT_DELETE_FILE, filePath, repository);
} catch (SQLException ex) {
throw new DatabaseAccessException("SQLException while trying to access the database\n" + ex);
}
}
public boolean deleteTerm(Long id) {
QueryRunner runner = DBUtilsHelper.getRunner();
;
try {
runner.update(DETELE_TERM_SQL, id);
} catch (SQLException e) {
logger.error(e + "");
}
return true;
}
public boolean updateTerm(Term term) {
QueryRunner runner = DBUtilsHelper.getRunner();
;
try {
runner.update(UPDATE_TERM_SQL, term.getName(), term.getId());
} catch (SQLException e) {
logger.error(e + "");
}
return true;
}
@Override
public void addOrderform(OrderFormVo orderform) throws SQLException {
String sql = "insert into orderform(userID, orderId, bookID, bookNum) value(?, ?, ?, ?) ";
runner.update(
conn,
sql,
orderform.getUserID(),
orderform.getOrderId(),
orderform.getBookID(),
orderform.getBookNum());
}
/**
* 调用queryrunner的update方法执行insert update delete语句并捕获异常
*
* @param sql
* @param params
* @return
*/
public boolean update(String sql, Object... params) {
try {
int n = queryRunner.update(sql, params);
if (n > 0) {
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
public void delete(int id, int userId) throws DAOException {
String sql = "DELETE FROM diettracker.weight dw WHERE dw.id = ? AND dw.userid = ?";
QueryRunner queryRunner = new QueryRunner(getDataSource());
Object[] params = {id, userId};
try {
queryRunner.update(sql, params);
} catch (SQLException e) {
logger.warn(e.getMessage());
throw new DAOException(e.getMessage(), e.getCause());
}
}
/**
* 更新
*
* @throws Exception
*/
@Test
public void testUpdate() throws Exception {
String sql = "delete from admin where id=?";
// 连接对象
conn = DbUtil.getConnection();
// 创建DbUtils核心工具类对象
QueryRunner qr = new QueryRunner();
qr.update(conn, sql, 26);
// 关闭
DbUtils.close(conn);
}
public static int operate(String sql, Object... params) { // 用于执行有参数的SQL语句
int result = 0;
QueryRunner runner = new QueryRunner();
try {
result = runner.update(getConnection(), sql, params); // 执行SQL语句
} catch (SQLException e) {
e.printStackTrace();
} finally {
DbUtils.closeQuietly(conn); // 关闭连接
}
return result;
}
@Override
public int update(Connection conn, String sql, Object[] params) throws SQLException {
if (logger.isDebugEnabled()) {
logger.debug(String.format("update(conn=%s, sql=%s, params=%s) - start ", conn, sql, params));
}
QueryRunner qRunner = new QueryRunner();
int rows = qRunner.update(conn, sql, params);
if (logger.isDebugEnabled()) {
logger.debug(String.format("update(rows=%s) - end ", rows));
}
return rows;
}
public static void test_insert() throws SQLException {
System.out.println("-------------test_insert()-------------");
// 创建连接
Connection conn = DBUtils.getConnection();
// 创建SQL执行工具
QueryRunner qRunner = new QueryRunner();
// 执行SQL插入
int n = qRunner.update(conn, "insert into user(name,pswd) values('iii','iii')");
System.out.println("成功插入" + n + "条数据！");
DBUtils.close(null, null, conn);
;
}