private static boolean connectToDatabase() {
String databaseConnectionInfo =
"jdbc:mysql://localhost/authorities?user=authorities&password=authorities&useUnicode=yes&characterEncoding=UTF-8";
if (authoritiesConn == null) {
try {
authoritiesConn = DriverManager.getConnection(databaseConnectionInfo);
getPreferredAuthorByOriginalNameStmt =
authoritiesConn.prepareStatement(
"SELECT viafId, normalizedName from preferred_authors where originalName = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
getPreferredAuthorByNormalizedNameStmt =
authoritiesConn.prepareStatement(
"SELECT viafId, normalizedName from preferred_authors where originalName = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
getPreferredAuthorByAlternateNameStmt =
authoritiesConn.prepareStatement(
"SELECT preferred_authors.viafId, normalizedName FROM `preferred_authors` inner join alternate_authors on preferred_authors.viafId = alternate_authors.viafId where alternateName = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
return true;
} catch (Exception e) {
logger.error("Error connecting to authorities database");
return false;
}
}
return true;
}
/** Return the user's secure_hash_key. If one wasn't found create one on the fly. */
private SecureHashKeyResult getSecureHashKey(UserAccount user, Connection c) throws SQLException {
PreparedStatement select = null;
// Create lazily.
PreparedStatement update = null;
int userId = user.getUserID();
boolean justCreated = false;
byte[] key = null;
try {
// TODO: consider having UserManager returning secure_hash_key.
// TODO: We have similar logic in several places for creating secure_hash_key just-in-time.
// D.R.Y. this out. Sorry I couldn't resist using this cliche :)
select = c.prepareStatement("SELECT secure_hash_key FROM dat_user_account WHERE object_id=?");
select.setInt(1, userId);
ResultSet rs = select.executeQuery();
if (!rs.next()) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("dat_user_account row not found");
lmg.param(LoggingConsts.USER_ID, userId);
// possible that the user simply disappeared by the time we got here.
m_logCategory.warn(lmg.toString());
} else {
key = rs.getBytes(1);
if (key == null || key.length == 0) {
// hash key not found; create one on the fly.
update =
c.prepareStatement("UPDATE dat_user_account SET secure_hash_key=? WHERE object_id=?");
key = createNewRandomKey();
update.setBytes(1, key);
update.setInt(2, userId);
int ct = update.executeUpdate();
if (ct != 1) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("Unable to update dat_user_account.secure_hash_key");
lmg.param(LoggingConsts.USER_ID, userId);
m_logCategory.error(lmg.toString());
} else {
justCreated = true;
}
} // needed to set key.
} // user found
} finally {
DbUtils.safeClose(select);
DbUtils.safeClose(update);
}
return new SecureHashKeyResult(key, justCreated);
}
static {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql:///hospital", "root", "root");
pa = con.prepareStatement("insert into patient values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
pd = con.prepareStatement("delete from patient where pid=? ");
pl = con.prepareStatement("select * from patient order by pid");
pps = con.prepareStatement("select * from patient where pid=? ");
pps1 = con.prepareStatement("select * from patient where plnm= ?");
} catch (Exception e) {
System.out.println(e);
}
}
public void doSQL() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people;");
stat.executeUpdate("create table people (name, occupation);");
PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
prep.setString(1, "Wittgenstein");
prep.setString(2, "smartypants");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("job = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
public int update(ValueObject obj) throws SQLException {
if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : update() ");
}
PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;
Connection conn = null;
PreparedStatement ps = null;
int result = 0;
StringBuffer sb = new StringBuffer();
sb.append("update PT_KICA_ERR_LOG set ")
.append("U_D_FLAG = ")
.append(toDB(entity.getU_D_FLAG()))
.append(",")
.append("YYYYMMDD = ")
.append(toDB(entity.getYYYYMMDD()))
.append(",")
.append("TRANSHOUR = ")
.append(toDB(entity.getTRANSHOUR()))
.append(",")
.append("FILENAME = ")
.append(toDB(entity.getFILENAME()))
.append(",")
.append("ERRLOG = ")
.append(toDB(entity.getERRLOG()))
.append(",")
.append("RESULT_FLAG = ")
.append(toDB(entity.getRESULT_FLAG()))
.append(",")
.append("UPD_DT = ")
.append(toDB(entity.getUPD_DT()))
.append(",")
.append("INS_DT = ")
.append(toDB(entity.getINS_DT()))
.append(" where 1=1 ");
sb.append(" and SEQ = ").append(toDB(entity.getSEQ()));
KJFLog.sql(sb.toString());
try {
conn = this.getConnection();
ps = conn.prepareStatement(sb.toString());
int i = 1;
result = ps.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
if (ps != null) ps.close();
this.release(conn);
}
return result;
}
/** Returns event_id of newly created logging entry. */
private Integer logEvent(IClient client, String eventText, InputStream logFileSteam, Connection c)
throws SQLException {
PreparedStatement stmt = null;
int id = 0;
try {
long longId = getSequenceId("seq_client_log_event_ids", c);
if (longId > Integer.MAX_VALUE) {
// dat_client_log_events.event_id is a 32 bit integer numeric type.
// this is a bad problem; ensure it's logged; don't depend on whoever's catching
// the exception.
m_logger.log("seq_client_log_event_ids overflowed").error();
throw new IllegalStateException("seq_client_log_event_ids overflowed.");
}
id = (int) longId;
stmt =
c.prepareStatement(
"insert into dat_client_log_events "
+ "(event_id, customer_id, user_id, event_time, description, has_log_file) "
+ "values "
+ "(?, ?, ?, current_timestamp, ?, ?)");
stmt.setInt(1, id);
stmt.setInt(2, client.getCustomerId());
stmt.setInt(3, client.getUserId());
stmt.setString(4, eventText);
stmt.setInt(5, logFileSteam == null ? 0 : 1);
if (stmt.executeUpdate() != 1) {
throw new SQLException("Can't insert client event for " + client);
}
} finally {
DbUtils.safeClose(stmt);
}
return new Integer(id);
}
public int delete(ValueObject obj) throws SQLException {
if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : delete() ");
}
PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;
Connection conn = null;
PreparedStatement ps = null;
int result = 0;
StringBuffer sb = new StringBuffer();
sb.append("delete from PT_KICA_ERR_LOG where 1=1")
.append(" and SEQ = ")
.append(toDB(entity.getSEQ()));
KJFLog.sql(sb.toString());
try {
conn = this.getConnection();
ps = conn.prepareStatement(sb.toString());
result = ps.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
if (ps != null) ps.close();
this.release(conn);
}
return result;
}
static void task1() throws FileNotFoundException, IOException, SQLException {
// Read Input
System.out.println("Task1 Started...");
BufferedReader br = new BufferedReader(new FileReader(inputFile));
br.readLine();
String task1Input = br.readLine();
br.close();
double supportPercent =
Double.parseDouble(task1Input.split(":")[1].split("=")[1].split("%")[0].trim());
if (supportPercent >= 0) {
System.out.println("Task1 Support Percent :" + supportPercent);
// Prepare query
String task1Sql =
"select temp.iname,(temp.counttrans/temp2.uniquetrans)*100 as percent"
+ " from (select i.itemname iname,count(t.transid) counttrans from trans t, items i"
+ " where i.itemid = t.itemid group by i.itemname having count(t.transid)>=(select count(distinct transid)*"
+ supportPercent / 100
+ " from trans)"
+ ") temp , (select count(distinct transid) uniquetrans from trans) temp2 order by percent";
PreparedStatement selTask1 = con.prepareStatement(task1Sql);
ResultSet rsTask1 = selTask1.executeQuery();
BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.1"));
while (rsTask1.next()) {
bw.write("{" + rsTask1.getString(1) + "}, s=" + rsTask1.getDouble(2) + "%");
bw.newLine();
}
rsTask1.close();
bw.close();
System.out.println("Task1 Completed...\n");
} else System.out.println("Support percent should be a positive number");
}
public void executeBatch(String sql, Object args[][]) throws SQLException, FileNotFoundException {
PreparedStatement prep = connection.prepareStatement(sql);
int i;
for (Object[] a : args) {
i = 1;
for (Object b : a) {
if (b instanceof String) {
prep.setString(i, (String) b);
}
if (b instanceof Integer) {
prep.setInt(i, (Integer) b);
}
if (b instanceof File) {
FileInputStream fis = new FileInputStream((File) b);
prep.setBinaryStream(i, fis);
}
if (b instanceof FileInputStream) {
prep.setBinaryStream(i, (InputStream) b);
}
if (b instanceof BufferedImage) {
byte[] buffer =
((DataBufferByte) ((BufferedImage) b).getRaster().getDataBuffer()).getData();
InputStream ist = new ByteArrayInputStream(buffer);
prep.setBinaryStream(i, ist);
}
i++;
}
prep.addBatch();
}
connection.setAutoCommit(false);
prep.executeBatch();
connection.setAutoCommit(true);
}
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con =
DriverManager.getConnection(Utility.connection, Utility.username, Utility.password);
int user_id = Integer.parseInt(request.getParameter("user_id"));
int question_id = Integer.parseInt(request.getParameter("question_id"));
int option = Integer.parseInt(request.getParameter("option"));
System.out.println("uid: " + user_id + "\nquestion: " + question_id + "\noption: " + option);
String str1 = "INSERT INTO VOTES(USER_ID, QUESTION_ID,OPTION_VOTED) VALUES (?,?,?)";
PreparedStatement prep1 = con.prepareStatement(str1);
prep1.setInt(1, user_id);
prep1.setInt(3, option);
prep1.setInt(2, question_id);
prep1.execute();
String str2 = "SELECT OPTION_" + option + " FROM ARCHIVE_VOTES WHERE QUESTION_ID=?";
PreparedStatement prep2 = con.prepareStatement(str2);
prep2.setInt(1, question_id);
int count = 0;
ResultSet rs2 = prep2.executeQuery();
if (rs2.next()) {
count = rs2.getInt("OPTION_" + option);
}
count++;
String str3 = "UPDATE ARCHIVE_VOTES SET OPTION_" + option + "=? WHERE QUESTION_ID=?";
PreparedStatement prep3 = con.prepareStatement(str3);
prep3.setInt(1, count);
prep3.setInt(2, question_id);
prep3.executeUpdate();
out.print("You Vote has been recorded! Thank you!");
System.out.println(
"Voted for question " + question_id + ", by user " + user_id + ", for option " + option);
} catch (Exception e) {
e.printStackTrace();
} finally {
out.close();
}
}
/**
* This internal method creates a new table in the database for storing XML responses. You can
* override this method in a subclass if this generic method does not work with the database
* server you are using, given that the following table columns are present:
*
* <ul>
* <li><code>id</code> - The primary key, which is used to identify cache entries (see {@link
* Cache#createCacheEntryName}
* <li><code>expiration_date</code> - A timestamp field for this cache entry's expiration date
* <li><code>response</code> - The actual response XML
* </ul>
*
* If you choose to use a different schema in your table you'll most likely have to override the
* other methods in this class, too.
*
* @throws SQLException When the generic SQL code in this method is not compatible with the
* database
*/
protected void createTable() throws SQLException {
PreparedStatement stmt =
connection.prepareStatement(
"CREATE TABLE "
+ tableName
+ " (id VARCHAR(200) PRIMARY KEY, expiration_date TIMESTAMP, response TEXT)");
stmt.execute();
stmt.close();
}
public void clear() {
try {
PreparedStatement stmt = connection.prepareStatement("DELETE FROM " + tableName);
stmt.execute();
stmt.close();
} catch (SQLException e) {
// ignore
}
}
private void insertLog(HttpServletRequest req, Connection connection) throws SQLException {
try (PreparedStatement stmt =
connection.prepareStatement("INSERT INTO LOGGING (date,ip,url) VALUES (?,?,?)")) {
stmt.setTimestamp(1, new Timestamp((new java.util.Date()).getTime()));
stmt.setString(2, req.getRemoteAddr());
stmt.setString(3, req.getRequestURI());
stmt.executeUpdate();
}
}
public void remove(String cacheEntryName) {
try {
PreparedStatement stmt =
connection.prepareStatement("DELETE FROM " + tableName + " WHERE id = ?");
stmt.setString(1, cacheEntryName);
stmt.execute();
stmt.close();
} catch (SQLException e) {
// ignore
}
}
public int insert(ValueObject obj) throws SQLException {
if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : insert() ");
}
PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;
Connection conn = null;
PreparedStatement ps = null;
int result = 0;
StringBuffer sb = new StringBuffer();
sb.append("insert into PT_KICA_ERR_LOG ")
.append(" ( SEQ,U_D_FLAG,YYYYMMDD,TRANSHOUR,FILENAME,ERRLOG,RESULT_FLAG,UPD_DT,INS_DT ) ")
.append(" values ( ")
.append(toDB(entity.getSEQ()))
.append(",")
.append(toDB(entity.getU_D_FLAG()))
.append(",")
.append(toDB(entity.getYYYYMMDD()))
.append(",")
.append(toDB(entity.getTRANSHOUR()))
.append(",")
.append(toDB(entity.getFILENAME()))
.append(",")
.append(toDB(entity.getERRLOG()))
.append(",")
.append(toDB(entity.getRESULT_FLAG()))
.append(",")
.append(toDB(entity.getUPD_DT()))
.append(",")
.append(toDB(entity.getINS_DT()))
.append(" ) ");
KJFLog.sql(sb.toString());
try {
conn = this.getConnection();
ps = conn.prepareStatement(sb.toString());
int i = 1;
result = ps.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
if (ps != null) ps.close();
this.release(conn);
}
return result;
}
public TableXRef(Connection c, String t, String match, String ret) throws SQLException {
cxn = c;
tableName = t;
matchField = match;
returnField = ret;
ps =
cxn.prepareStatement(
"select " + returnField + " from " + tableName + " " + "where " + matchField + "=?");
}
public int update(ValueObject obj) throws SQLException {
if ((obj instanceof PT_BBS_COM_FILESEntity) == false) {
throw new SQLException("DAO 에러(1): PT_BBS_COM_FILES : update() ");
}
PT_BBS_COM_FILESEntity entity = (PT_BBS_COM_FILESEntity) obj;
Connection conn = null;
PreparedStatement ps = null;
int result = 0;
StringBuffer sb = new StringBuffer();
sb.append("update PT_BBS_COM_FILES set ")
.append("CT_ID = ")
.append(toDB(entity.getCT_ID()))
.append(",")
.append("BOARD_SEQ = ")
.append(toDB(entity.getBOARD_SEQ()))
.append(",")
.append("FILE_REAL_NAME = ")
.append(toDB(entity.getFILE_REAL_NAME()))
.append(",")
.append("FILE_SIZE = ")
.append(toDB(entity.getFILE_SIZE()))
.append(",")
.append("UPD_DT = ")
.append(toDB(entity.getUPD_DT()))
.append(",")
.append("DOWN_HIT = ")
.append(toDB(entity.getDOWN_HIT()))
.append(" where 1=1 ");
sb.append(" and SYS_FILE_NAME = ").append(toDB(entity.getSYS_FILE_NAME()));
KJFLog.sql(sb.toString());
try {
conn = this.getConnection();
ps = conn.prepareStatement(sb.toString());
int i = 1;
result = ps.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
if (ps != null) ps.close();
this.release(conn);
}
return result;
}
Competition getDataFromDatabase(String arg, Properties properties) {
Competition competition = new Competition();
String getAthleteDataQuery =
"SELECT athletes.name, athletes.dob, athletes.country_code, "
+ "results.race_100m, results.long_jump, results.shot_put, results.high_jump, results.race_400m, "
+ "results.hurdles_110m, results.discus_throw, results.pole_vault, results.javelin_throw, results.race_1500m "
+ "FROM athletes, results, competitions "
+ "WHERE athletes.id = results.athlete_id "
+ "AND results.competition_id = competitions.id "
+ "AND (competitions.id = ? OR competitions.name = ?)";
Connection conn = null;
try {
String url = properties.getProperty("db.url");
String username = properties.getProperty("db.username");
String password = properties.getProperty("db.password");
conn = DriverManager.getConnection(url, username, password);
PreparedStatement athleteDataQuery = conn.prepareStatement(getAthleteDataQuery);
athleteDataQuery.setString(1, arg);
athleteDataQuery.setString(2, arg);
ResultSet resultSet = athleteDataQuery.executeQuery();
while (resultSet.next()) {
Athlete athlete = parseAthlete(resultSet);
if (athlete == null) {
System.out.println(Error.ERROR_DB_ATHLETE_PARSING_FAILED.getErrorText());
continue;
}
DecathlonEvents decathlonEvents = parseDecathlonEvents(resultSet);
if (decathlonEvents == null) {
System.out.println(Error.ERROR_DB_ATHLETE_RESULTS_PARSING_FAILED.getErrorText());
continue;
}
athlete.setDecathlonEvent(decathlonEvents);
competition.addAthlete(athlete);
}
} catch (SQLException e) {
System.out.println(Error.ERROR_DB_CONNECTION.getErrorText());
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
System.out.println(Error.ERROR_DB_CLOSE.getErrorText());
}
}
return competition;
}
/**
* Queries persons older than provided age.
*
* @param conn JDBC connection.
* @param minAge Minimum age.
* @throws SQLException In case of SQL error.
*/
private static void queryPersons(Connection conn, int minAge) throws SQLException {
PreparedStatement stmt = conn.prepareStatement("select name, age from Person where age >= ?");
stmt.setInt(1, minAge);
ResultSet rs = stmt.executeQuery();
X.println(">>> Persons older than " + minAge + ":");
while (rs.next())
X.println(">>> " + rs.getString("NAME") + " (" + rs.getInt("AGE") + " years old)");
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
Connection conn = null;
PreparedStatement pstmt = null;
try {
System.out.println("Enrollno: 130050131049");
// STEP 2: Register JDBC driver
Class.forName(JDBC_DRIVER);
// STEP 3: Open a connection
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
// STEP 2: Executing query
String sql = "SELECT * FROM logindetails WHERE name = ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "Krut");
ResultSet rs = pstmt.executeQuery();
out.print("| <b>Name</b>| ");
out.print("<b>Password</b>| ");
out.println("</br>\n-------------------------------</br>");
while (rs.next()) {
out.println();
out.print("| " + rs.getString(1));
out.print("| " + rs.getString(2) + "|");
out.println("</br>");
}
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// finally block used to close resources
try {
if (pstmt != null) conn.close();
} catch (SQLException se) {
} // do nothing
try {
if (conn != null) conn.close();
} catch (SQLException se) {
se.printStackTrace();
} // end finally try
} // end try
}
public boolean contains(String cacheEntryName) {
try {
PreparedStatement stmt =
connection.prepareStatement("SELECT id FROM " + tableName + " WHERE id = ?");
stmt.setString(1, cacheEntryName);
ResultSet result = stmt.executeQuery();
boolean b = result.next();
stmt.close();
return b;
} catch (SQLException e) {
return false;
}
}
/**
* Method called by the Form panel to delete existing data.
*
* @param persistentObject value object to delete
* @return an ErrorResponse value object in case of errors, VOResponse if the operation is
* successfully completed
*/
public Response deleteRecord(ValueObject persistentObject) throws Exception {
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement("delete from DEMO4 where TEXT=?");
DetailTestVO vo = (DetailTestVO) persistentObject;
stmt.setString(1, vo.getStringValue());
stmt.execute();
// this instruction is no more needed: the grid has been linked to the Form (see Form.linkGrid
// method...)
// gridFrame.reloadData();
return new VOResponse(vo);
} catch (SQLException ex) {
ex.printStackTrace();
return new ErrorResponse(ex.getMessage());
} finally {
try {
stmt.close();
conn.commit();
} catch (SQLException ex1) {
}
}
}
/**
* Queries persons working in provided organization.
*
* @param conn JDBC
* @param orgName Organization name.
* @throws SQLException In case of SQL error.
*/
private static void queryPersonsInOrganization(Connection conn, String orgName)
throws SQLException {
PreparedStatement stmt =
conn.prepareStatement(
"select p.name from Person p, Organization o where p.orgId = o.id and o.name = ?");
stmt.setString(1, orgName);
ResultSet rs = stmt.executeQuery();
X.println(">>> Persons working in " + orgName + ":");
while (rs.next()) X.println(">>> " + rs.getString(1));
}
public static void main(String[] args) throws SQLException {
// declare a connection by using Connection interface
Connection connection = null;
/* Create string of connection url within specified format with machine
name, port number and database name. Here machine name id localhost
and database name is mahendra. */
String connectionURL = "jdbc:mysql://localhost:3306/mahendra";
/*declare a resultSet that works as a table resulted by execute a specified
sql query. */
ResultSet rs = null;
// Declare prepare statement.
PreparedStatement psmnt = null;
// declare FileInputStream object to store binary stream of given image.
FileInputStream fis;
try {
// Load JDBC driver "com.mysql.jdbc.Driver"
Class.forName("com.mysql.jdbc.Driver").newInstance();
/* Create a connection by using getConnection() method that takes
parameters of string type connection url, user name and password to
connect to database. */
connection = DriverManager.getConnection(connectionURL, "root", "root");
// create a file object for image by specifying full path of image as parameter.
File image = new File("C:/image.jpg");
/* prepareStatement() is used for create statement object that is
used for sending sql statements to the specified database. */
psmnt =
connection.prepareStatement(
"insert into save_image(name, city, image, Phone) " + "values(?,?,?,?)");
psmnt.setString(1, "mahendra");
psmnt.setString(2, "Delhi");
psmnt.setString(4, "123456");
fis = new FileInputStream(image);
psmnt.setBinaryStream(3, (InputStream) fis, (int) (image.length()));
/* executeUpdate() method execute specified sql query. Here this query
insert data and image from specified address. */
int s = psmnt.executeUpdate();
if (s > 0) {
System.out.println("Uploaded successfully !");
} else {
System.out.println("unsucessfull to upload image.");
}
} // catch if found any exception during rum time.
catch (Exception ex) {
System.out.println("Found some error : " + ex);
} finally {
// close all the connections.
connection.close();
psmnt.close();
}
}
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con =
DriverManager.getConnection(Utility.connection, Utility.username, Utility.password);
String email = request.getParameter("email_id");
String number = "";
boolean exists = false;
String user_name = "";
int user_id = -1;
String str1 = "SELECT USER_ID,NAME,PHONE_NUMBER FROM USERS WHERE EMAIL_ID=?";
PreparedStatement prep1 = con.prepareStatement(str1);
prep1.setString(1, email);
ResultSet rs1 = prep1.executeQuery();
if (rs1.next()) {
exists = true;
user_id = rs1.getInt("USER_ID");
user_name = rs1.getString("NAME");
number = rs1.getString("PHONE_NUMBER");
}
int verification = 0;
JSONObject data = new JSONObject();
if (exists) {
verification = (int) (Math.random() * 9535641 % 999999);
System.out.println("Number " + number + "\nVerification: " + verification);
SMSProvider.sendSMS(
number, "Your One Time Verification Code for PeopleConnect Is " + verification);
}
data.put("user_name", user_name);
data.put("user_id", user_id);
data.put("verification_code", "" + verification);
data.put("phone_number", number);
String toSend = data.toJSONString();
out.print(toSend);
System.out.println(toSend);
} catch (Exception e) {
e.printStackTrace();
} finally {
out.close();
}
}
public static void main(String argv[]) {
Connection con = null;
PreparedStatement pstmt = null;
InputStream fin = null;
String url = "jdbc:oracle:thin:@localhost:1521:XE";
String userid = "hr1";
String passwd = "123456";
String picName[] = {"iconPet29.jpg", "iconPet30.png"};
int[] picPetId = {29, 30};
int rowsUpdated = 0;
try {
con = DriverManager.getConnection(url, userid, passwd);
for (int i = 0; i < picName.length; i++) {
File pic = new File("WebContent/images", picName[i]); // �۹���|- picFrom
// ������|- Ĵ�p:
// File pic = new File("x:\\aa\\bb\\picFrom", picName);
long flen = pic.length();
fin = new FileInputStream(pic);
pstmt = con.prepareStatement("UPDATE PET SET icon = ? WHERE ID = ?");
pstmt.setBinaryStream(1, fin, (int) flen); // void
// pstmt.setBinaryStream(int
// parameterIndex,
// InputStream x,
// int length)
// throws
// SQLException
pstmt.setInt(2, picPetId[i]);
rowsUpdated += pstmt.executeUpdate();
}
System.out.print("Changed " + rowsUpdated);
if (1 == rowsUpdated) System.out.println(" row.");
else System.out.println(" rows.");
fin.close();
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
}
}
}
@Override
public String getUsername(byte[] encodedKey) {
String b64key = Base64.getEncoder().encodeToString(encodedKey);
try {
try (PreparedStatement preparedStatement =
conn.prepareStatement("select name from users where publickey = ? limit 1")) {
preparedStatement.setString(1, b64key);
ResultSet resultSet = preparedStatement.executeQuery();
boolean next = resultSet.next();
if (!next) return "";
return resultSet.getString(1);
}
} catch (SQLException sqle) {
throw new IllegalStateException(sqle);
}
}
@Override
public void deleteOperation(int operationId) throws OperationManagementDAOException {
PreparedStatement stmt = null;
try {
super.deleteOperation(operationId);
Connection connection = OperationManagementDAOFactory.getConnection();
stmt = connection.prepareStatement("DELETE DM_POLICY_OPERATION WHERE OPERATION_ID=?");
stmt.setInt(1, operationId);
stmt.executeUpdate();
} catch (SQLException e) {
throw new OperationManagementDAOException(
"Error occurred while deleting operation metadata", e);
} finally {
OperationManagementDAOUtil.cleanupResources(stmt);
}
}
public static void main(String[] args) {
BD bd = new BD();
String lsTable = "pays";
String lsContenu = "";
lsContenu += "<?xml version='1.0' encoding='utf-8'?>\n";
lsContenu += "<!-- " + lsTable + "Document.xml -->\n";
lsContenu += "<" + lsTable + ">\n";
String lsEnfant = lsTable.substring(0, lsTable.length() - 1);
try {
bd.seConnecter("cours", "root", "alonso");
Connection lcn = bd.getConnexion();
PreparedStatement lpst =
lcn.prepareStatement("SELECT * FROM pays"); // paysSelect() ou villesSelect()
ResultSet lrs = lpst.executeQuery();
ResultSetMetaData lrsmd = lrs.getMetaData();
int liCols = lrsmd.getColumnCount();
while (lrs.next()) {
lsContenu += "\t<" + lsEnfant + " ";
for (int i = 1; i <= liCols; i++) {
lsContenu += lrsmd.getColumnName(i) + "='" + lrs.getString(i) + "' ";
}
lsContenu += "/>\n";
}
lsContenu += "</" + lsTable + ">";
System.out.println(lsContenu);
FileWriter lfwFichier = new FileWriter(lsTable + ".xml"); // pays.csv ou villes.csv
BufferedWriter lbwBuffer = new BufferedWriter(lfwFichier);
lbwBuffer.write(lsContenu);
lbwBuffer.close();
lfwFichier.close();
bd.seDeconnecter();
} catch (Exception e) {
System.out.println("Erreor : ");
}
}
public boolean isExpired(String cacheEntryName) {
try {
PreparedStatement stmt =
connection.prepareStatement("SELECT expiration_date FROM " + tableName + " WHERE id = ?");
stmt.setString(1, cacheEntryName);
ResultSet result = stmt.executeQuery();
if (result.next()) {
Timestamp timestamp = result.getTimestamp("expiration_date");
long expirationDate = timestamp.getTime();
stmt.close();
return expirationDate < System.currentTimeMillis();
}
} catch (SQLException e) {
// ignore
}
return false;
}
