Java Code Examples for java.sql.Connection.prepareStatement()

The following are Jave code examples for showing how to use prepareStatement() of the java.sql.Connection class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.
+ Save this method
Example 1
Project: Nird2   File: JdbcDatabase.java   View Source Code Vote up 7 votes
@Override
public Settings getSettings(Connection txn, String namespace)
		throws DbException {
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		String sql = "SELECT key, value FROM settings WHERE namespace = ?";
		ps = txn.prepareStatement(sql);
		ps.setString(1, namespace);
		rs = ps.executeQuery();
		Settings s = new Settings();
		while (rs.next()) s.put(rs.getString(1), rs.getString(2));
		rs.close();
		ps.close();
		return s;
	} catch (SQLException e) {
		tryToClose(rs);
		tryToClose(ps);
		throw new DbException(e);
	}
}
 
Example 2
Project: BibliotecaPS   File: StatementRegressionTest.java   View Source Code Vote up 6 votes
/**
 * Test Bug#75956 - Inserting timestamps using a server PreparedStatement and useLegacyDatetimeCode=false
 */
public void testBug75956() throws Exception {
    createTable("bug75956", "(id int not null primary key auto_increment, dt1 datetime, dt2 datetime)");
    Connection sspsConn = getConnectionWithProps("useCursorFetch=true,useLegacyDatetimeCode=false");
    this.pstmt = sspsConn.prepareStatement("insert into bug75956 (dt1, dt2) values (?, ?)");
    this.pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
    this.pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
    this.pstmt.addBatch();
    this.pstmt.clearParameters();
    this.pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
    this.pstmt.setTimestamp(2, null);
    this.pstmt.addBatch();
    this.pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
    this.pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
    this.pstmt.addBatch();
    this.pstmt.executeBatch();
    this.pstmt.close();
    this.rs = sspsConn.createStatement().executeQuery("select count(*) from bug75956 where dt2 is NULL");
    this.rs.next();
    assertEquals(1, this.rs.getInt(1));
    sspsConn.close();
}
 
Example 3
Project: AdamantineShield   File: InspectManager.java   View Source Code Vote up 6 votes
public synchronized void inspectContainer(Player p, UUID world, Vector3i pos) {
	Connection c = db.getConnection();
	ContainerLookupResult lookup = null;
	
	try {
		PreparedStatement ps = c.prepareStatement(QueryHelper.INSPECT_CONTAINER_QUERY);
		ps.setInt(1, pos.getX());
		ps.setInt(2, pos.getY());
		ps.setInt(3, pos.getZ());
		ps.setString(4, world.toString());
		ResultSet result = ps.executeQuery();
		
		lookup = new ContainerLookupResult(result);
		LookupResultManager.instance().setLookupResult(p, lookup);
		
		result.close();
		c.close();
	} catch (SQLException e) {
		e.printStackTrace();
		p.sendMessage(Text.of(TextColors.DARK_AQUA, "[AC] ", TextColors.RED, "A database error has occurred! Contact your server administrator!"));
		return;
	}
	
	lookup.showPage(p, 1);
}
 
Example 4
Project: MuralEscolar   File: MuralDAOImp.java   View Source Code Vote up 6 votes
@Override
public String inserir(Professor professor, String texto) {
    String sql = "insert into mural (texto, id_prof) values (?,?)";
    Connection conn = SQLiteConnectionFactory.getConnection();
    
    try{
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1,texto);
        pst.setInt(2,professor.getId());
        
        int res = pst.executeUpdate();
        if(res > 0){
            return "Conteudo salvo com sucesso!";
        }else{
            return "Erro ao inserir conteudo";
        }
    }catch(SQLException e){
        return e.getMessage();
    }finally{
        SQLiteConnectionFactory.close(conn);
    }
    
}
 
Example 5
Project: Queue-Manager-Cloud-Dispatcher   File: TemplateDAO.java   View Source Code Vote up 6 votes
/**
   * Get the number of incident for a specific component in the table.
   */
  public int getAmount(String component, int id) throws SQLException {
      Connection connection = dataSource.getConnection();
      ResultSet rs = null;
      
      try {
          PreparedStatement pstmt = connection
                  .prepareStatement("SELECT " + component
                  				+ " FROM ROOT"
                  				+ " WHERE ID=?");
          pstmt.setInt(1, id);
          rs = pstmt.executeQuery();

          if (rs.next()) {
          	return rs.getInt(1);
          }
      } finally {
          if (connection != null) {
              connection.close();
          }
      }
return -1;
  }
 
Example 6
Project: asura   File: StdJDBCDelegate.java   View Source Code Vote up 6 votes
/**
 * <p>
 * Select the listeners associated with a given trigger.
 * </p>
 * 
 * @param conn
 *          the DB Connection
 * @param triggerName
 *          the name of the trigger
 * @param groupName
 *          the group containing the trigger
 * @return array of <code>String</code> trigger listener names
 */
public String[] selectTriggerListeners(Connection conn, String triggerName,
        String groupName) throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        ps = conn.prepareStatement(rtp(SELECT_TRIGGER_LISTENERS));
        ps.setString(1, triggerName);
        ps.setString(2, groupName);
        rs = ps.executeQuery();

        ArrayList list = new ArrayList();
        while (rs.next()) {
            list.add(rs.getString(1));
        }
        Object[] oArr = list.toArray();
        String[] sArr = new String[oArr.length];
        System.arraycopy(oArr, 0, sArr, 0, oArr.length);
        return sArr;
    } finally {
        closeResultSet(rs);
        closeStatement(ps);
    }
}
 
Example 7
Project: asura   File: StdJDBCDelegate.java   View Source Code Vote up 6 votes
public int selectJobExecutionCount(Connection conn, String jobName,
        String jobGroup) throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        ps = conn.prepareStatement(rtp(SELECT_JOB_EXECUTION_COUNT));
        ps.setString(1, jobName);
        ps.setString(2, jobGroup);

        rs = ps.executeQuery();

        return (rs.next()) ? rs.getInt(1) : 0;
    } finally {
        closeResultSet(rs);
        closeStatement(ps);
    }
}
 
Example 8
Project: AeroStory   File: MapleCharacter.java   View Source Code Vote up 5 votes
public void setPandoraLog(String itemid) {
Connection con1 = DatabaseConnection.getConnection();
try {
PreparedStatement ps;
ps = con1.prepareStatement("insert into pandoralog (characterid, itemid) values (?,?)");
ps.setInt(1, id);
ps.setString(2, itemid);
ps.executeUpdate();
ps.close();
} catch (Exception Ex) {
	System.out.print("Error setting the Pandora Log.");
}
}
 
Example 9
Project: ChemistryAdministrativePortal   File: UserHelper.java   View Source Code Vote up 5 votes
/**
 * Constructor which makes a connection
 */
public UserHelper() {
	try {
		//Set up connection
		Class.forName("com.mysql.jdbc.Driver");
		//CHECK HERE FOR CONNECTION PROBLEM
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/chemistrydatabase", "root", "root");
		
		//Create the preparedstatement(s)
		authenticateUserStatement = conn.prepareStatement("select * from user where email=? and password=?");
	} catch (Exception e) {
		System.out.println(e.getClass().getName() + ": " + e.getMessage());
	}
}
 
Example 10
Project: holon-datastore-jdbc   File: DefaultJdbcDatastore.java   View Source Code Vote up 5 votes
/**
 * Create a {@link PreparedStatement} for an INSERT operation configuring generated keys.
 * @param connection Connection
 * @param dialect Dialect
 * @param sql SQL statement
 * @param pkNames Optional primary key column names
 * @return Configured statement
 * @throws SQLException If an error occurred
 */
private PreparedStatement createInsertStatement(Connection connection, JdbcDialect dialect, String sql,
		String[] pkNames) throws SQLException {
	if (dialect.supportsGetGeneratedKeys()) {
		if (getDialect().supportGetGeneratedKeyByName() && pkNames != null && pkNames.length > 0) {
			return connection.prepareStatement(sql, pkNames);
		} else {
			return connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		}
	}
	return connection.prepareStatement(sql);
}
 
Example 11
Project: BiliInfoCrawler   File: JDBC.java   View Source Code Vote up 5 votes
public static void insertBilibiliData(Connection conn, Bilibili bilibili) throws SQLException{
	String insertSQL = "INSERT INTO bilibili (aid,tid,title,tname,favorite,coin,author) VALUES (?,?,?,?,?,?,?)";
	PreparedStatement ps = conn.prepareStatement(insertSQL);
	ps.setInt(1, bilibili.getAid());
	ps.setInt(2, bilibili.getTid());
	ps.setString(3, bilibili.getTitle());
	ps.setString(4, bilibili.getTname());
	ps.setInt(5, bilibili.getFavorite());
	ps.setInt(6, bilibili.getCoin());
	ps.setString(7, bilibili.getAuthor());
	ps.execute();
}
 
Example 12
Project: ofmeet-openfire-plugin   File: CallLogDAO.java   View Source Code Vote up 5 votes
/**
 * Insert a new CallLog into the database
 *
 * @param callLog call logging
 * @throws SQLException
 */
public static void insert(CallLog callLog) throws SQLException {

    String sql = "INSERT INTO ofSipPhoneLog (username, addressFrom, addressTo, datetime, duration, calltype) "
            + " values  (?, ?, ?, ?, ?, ?)";

    Connection con = null;
    PreparedStatement psmt = null;
    ResultSet rs = null;

    try {
        con = DbConnectionManager.getConnection();
        psmt = con.prepareStatement(sql);
        psmt.setString(1, callLog.getUsername());
        psmt.setString(2, callLog.getNumA());
        psmt.setString(3, callLog.getNumB());
        psmt.setLong(4, callLog.getDateTime());
        psmt.setInt(5, callLog.getDuration());
        psmt.setString(6, callLog.getType().name());

        psmt.executeUpdate();

    } catch (SQLException e) {
        Log.error(e.getMessage(), e);
        throw new SQLException(e.getMessage());
    } finally {
        DbConnectionManager.closeConnection(rs, psmt, con);
    }

}
 
Example 13
Project: spr   File: SqlUpdates.java   View Source Code Vote up 5 votes
public static boolean updateTipoDocumento(TipoDocumento tipoDocumento,String usuarioResponsable) {
	try {
		Connection conect = ConnectionConfiguration.conectar();

		String query = "update tipo_documentos set ";
		PreparedStatement update = null;
		
		if (tipoDocumento.getNombre() != "") {
			query += " nombre = ?, ";
		}
		{
			query += " usuario_responsable = ?, ";
		}

		query = query.substring(0, query.length() - 2);
		query += " where id = ?";

		int cantCampos = 0;
		update = conect.prepareStatement(query);
		
		if (tipoDocumento.getNombre() != "") {
			cantCampos++;
			update.setString(cantCampos, tipoDocumento.getNombre());
		}
		cantCampos++;
		update.setString(cantCampos, usuarioResponsable);

		cantCampos++;
		update.setInt(cantCampos, tipoDocumento.getId());

		update.execute();
		conect.close();
		return true;
	} catch (SQLException e) {
		e.printStackTrace();
		return false;
	}
}
 
Example 14
Project: tangyuan2   File: SqlActuator.java   View Source Code Vote up 5 votes
public List<Map<String, Object>> selectAll(Connection connection, String sql, List<Object> args) throws SQLException {
	PreparedStatement ps = connection.prepareStatement(sql);
	try {
		setParameters(ps, args);
		ResultSet rs = ps.executeQuery();
		return getResults(rs, null);
	} finally {
		try {
			ps.close();
		} catch (SQLException e) {
			// ignore
		}
	}
}
 
Example 15
Project: L2jBrasil   File: L2Clan.java   View Source Code Vote up 5 votes
/**
 * @return Returns the noticeEnabled.
 */
public boolean isNoticeEnabled()
{
 String result="";
 Connection con = null;
 try
 {
    
	 con = L2DatabaseFactory.getInstance().getConnection();
	 PreparedStatement statement = con.prepareStatement("SELECT enabled FROM clan_notices WHERE clanID=?");
	 statement.setInt(1, getClanId());
	 ResultSet rset = statement.executeQuery();
    
	 while (rset.next())
	 {
		 result = rset.getString("enabled");
	 }
    
	 rset.close();
	 statement.close();
	 con.close();
    
 } catch (Exception e)
 {
	 if (Config.DEBUG)
	 System.out.println("BBS: Error while reading _noticeEnabled for clan "+ this.getClanId() + "");
	 if(e.getMessage()!=null)
		 if (Config.DEBUG) 
		 System.out.println("BBS: Exception = "+e.getMessage()+"");
 }
 if (result.isEmpty())
 {
	 insertNotice();
	 return false;
 }
 else if(result.compareToIgnoreCase("true")==0)
	 return true;
 else
	 return false;
}
 
Example 16
Project: AeroStory   File: CashShop.java   View Source Code Vote up 4 votes
public CashShop(int accountId, int characterId, int jobType) throws SQLException {
    this.accountId = accountId;
    this.characterId = characterId;

    if (jobType == 0) {
        factory = ItemFactory.CASH_EXPLORER;
    } else if (jobType == 1) {
        factory = ItemFactory.CASH_CYGNUS;
    } else if (jobType == 2) {
        factory = ItemFactory.CASH_ARAN;
    }

    Connection con = DatabaseConnection.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        ps = con.prepareStatement("SELECT `nxCredit`, `maplePoint`, `nxPrepaid` FROM `accounts` WHERE `id` = ?");
        ps.setInt(1, accountId);
        rs = ps.executeQuery();

        if (rs.next()) {
            this.nxCredit = rs.getInt("nxCredit");
            this.maplePoint = rs.getInt("maplePoint");
            this.nxPrepaid = rs.getInt("nxPrepaid");
        }

        rs.close();
        ps.close();

        for (Pair<Item, MapleInventoryType> item : factory.loadItems(accountId, false)) {
            inventory.add(item.getLeft());
        }

        ps = con.prepareStatement("SELECT `sn` FROM `wishlists` WHERE `charid` = ?");
        ps.setInt(1, characterId);
        rs = ps.executeQuery();

        while (rs.next()) {
            wishList.add(rs.getInt("sn"));
        }

        rs.close();
        ps.close();
    } finally {
        if (ps != null) ps.close();
        if (rs != null) rs.close();
    }
}
 
Example 17
Project: aliyun-maxcompute-data-collectors   File: OraOopOracleQueries.java   View Source Code Vote up 4 votes
public static void createExportTableFromTemplateWithPartitioning(
    Connection connection, OracleTable newTable, String tableStorageClause,
    OracleTable templateTable, boolean noLogging, String partitionName,
    Object jobDateTime, int numberOfMappers, String[] subPartitionNames)
    throws SQLException {

  String dateFormat = "yyyy-mm-dd hh24:mi:ss";

  Object partitionBound =
      OraOopOracleQueries.oraDATEAddJulianDays(jobDateTime, 0, 1);

  String partitionBoundStr =
      OraOopOracleQueries.oraDATEToString(partitionBound, dateFormat);

  StringBuilder subPartitions = new StringBuilder();
  for (int idx = 0; idx < numberOfMappers; idx++) {
    if (idx > 0) {
      subPartitions.append(",");
    }

    subPartitions.append(String.format(" SUBPARTITION %s VALUES (%d)",
        subPartitionNames[idx], idx));
  }

  String sql =
      String.format(
          "CREATE TABLE %s \n" + "%s %s \n" + "PARTITION BY RANGE (%s) \n"
              + "SUBPARTITION BY LIST (%s) \n" + "(PARTITION %s \n"
              + "VALUES LESS THAN (to_date('%s', '%s')) \n" + "( %s ) \n"
              + ") \n" + "AS \n"
              + "(SELECT t.*, sysdate %s, 0 %s, 0 %s FROM %s t \n"
              + "WHERE 0=1)", newTable.toString(), noLogging ? "NOLOGGING"
              : "", tableStorageClause,
          OraOopConstants.COLUMN_NAME_EXPORT_PARTITION,
          OraOopConstants.COLUMN_NAME_EXPORT_SUBPARTITION, partitionName,
          partitionBoundStr, dateFormat, subPartitions.toString(),
          OraOopConstants.COLUMN_NAME_EXPORT_PARTITION,
          OraOopConstants.COLUMN_NAME_EXPORT_SUBPARTITION,
          OraOopConstants.COLUMN_NAME_EXPORT_MAPPER_ROW, templateTable
              .toString());

  LOG.debug(String.format("SQL generated by %s:\n%s", OraOopUtilities
      .getCurrentMethodName(), sql));

  try {

    // Create the main export table...
    PreparedStatement preparedStatement = connection.prepareStatement(sql);
    preparedStatement.execute(sql);
    preparedStatement.close();
  } catch (SQLException ex) {
    LOG.error(String
        .format(
            "The error \"%s\" was encountered when executing the following "
            + "SQL statement:\n%s",
            ex.getMessage(), sql));
    throw ex;
  }
}
 
Example 18
Project: BibliotecaPS   File: StatementsTest.java   View Source Code Vote up 4 votes
/**
 * Tests for ResultSet.updateNClob()
 * 
 * @throws Exception
 */
public void testUpdateNClob() throws Exception {
    createTable("testUpdateNChlob", "(c1 CHAR(10) PRIMARY KEY, c2 NATIONAL CHARACTER(10)) default character set sjis");
    Properties props1 = new Properties();
    props1.put("useServerPrepStmts", "true"); // use server-side prepared statement
    props1.put("characterEncoding", "UTF-8"); // ensure charset isn't utf8 here
    Connection conn1 = getConnectionWithProps(props1);
    PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testUpdateNChlob (c1, c2) VALUES (?, ?)");
    pstmt1.setString(1, "1");
    NClob nClob1 = conn1.createNClob();
    nClob1.setString(1, "aaa");
    pstmt1.setNClob(2, nClob1);
    pstmt1.execute();
    Statement stmt1 = conn1.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs1 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
    rs1.next();
    NClob nClob2 = conn1.createNClob();
    nClob2.setString(1, "bbb");
    rs1.updateNClob("c2", nClob2);
    rs1.updateRow();
    rs1.moveToInsertRow();
    rs1.updateString("c1", "2");
    NClob nClob3 = conn1.createNClob();
    nClob3.setString(1, "ccc");
    rs1.updateNClob("c2", nClob3);
    rs1.insertRow();
    ResultSet rs2 = stmt1.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
    rs2.next();
    assertEquals("1", rs2.getString("c1"));
    assertEquals("bbb", rs2.getNString("c2"));
    rs2.next();
    assertEquals("2", rs2.getString("c1"));
    assertEquals("ccc", rs2.getNString("c2"));
    pstmt1.close();
    stmt1.close();
    conn1.close();

    createTable("testUpdateNChlob", "(c1 CHAR(10) PRIMARY KEY, c2 CHAR(10)) default character set sjis"); // sjis field
    Properties props2 = new Properties();
    props2.put("useServerPrepStmts", "true"); // use server-side prepared statement
    props2.put("characterEncoding", "SJIS"); // ensure charset isn't utf8 here
    Connection conn2 = getConnectionWithProps(props2);
    PreparedStatement pstmt2 = conn2.prepareStatement("INSERT INTO testUpdateNChlob (c1, c2) VALUES (?, ?)");
    pstmt2.setString(1, "1");
    pstmt2.setString(2, "aaa");
    pstmt2.execute();
    Statement stmt2 = conn2.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs3 = stmt2.executeQuery("SELECT c1, c2 FROM testUpdateNChlob");
    rs3.next();
    NClob nClob4 = conn2.createNClob();
    nClob4.setString(1, "bbb");
    try {
        rs3.updateNClob("c2", nClob4); // field's charset isn't utf8
        fail();
    } catch (SQLException ex) {
        assertEquals("Can not call updateNClob() when field's character set isn't UTF-8", ex.getMessage());
    }
    rs3.close();
    pstmt2.close();
    stmt2.close();
    conn2.close();
}
 
Example 19
Project: sample.daytrader3   File: TradeDirect.java   View Source Code Vote up 4 votes
private PreparedStatement getStatement(Connection conn, String sql,
		int type, int concurrency) throws Exception {
	return conn.prepareStatement(sql, type, concurrency);
}
 
Example 20
Project: the-vigilantes   File: StatementRegressionTest.java   View Source Code Vote up 4 votes
/**
 * Tests fix for BUG#11663, autoGenerateTestcaseScript uses bogus parameter
 * names for server-side prepared statements.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug11663() throws Exception {
    if (versionMeetsMinimum(4, 1, 0) && ((com.mysql.jdbc.Connection) this.conn).getUseServerPreparedStmts()) {
        Connection testcaseGenCon = null;
        PrintStream oldErr = System.err;

        try {
            createTable("testBug11663", "(field1 int)");

            Properties props = new Properties();
            props.setProperty("autoGenerateTestcaseScript", "true");
            testcaseGenCon = getConnectionWithProps(props);
            ByteArrayOutputStream testStream = new ByteArrayOutputStream();
            PrintStream testErr = new PrintStream(testStream);
            System.setErr(testErr);
            this.pstmt = testcaseGenCon.prepareStatement("SELECT field1 FROM testBug11663 WHERE field1=?");
            this.pstmt.setInt(1, 1);
            this.pstmt.execute();
            System.setErr(oldErr);
            String testString = new String(testStream.toByteArray());

            int setIndex = testString.indexOf("SET @debug_stmt_param");
            int equalsIndex = testString.indexOf("=", setIndex);
            String paramName = testString.substring(setIndex + 4, equalsIndex);

            int usingIndex = testString.indexOf("USING " + paramName, equalsIndex);

            assertTrue(usingIndex != -1);
        } finally {
            System.setErr(oldErr);

            if (this.pstmt != null) {
                this.pstmt.close();
                this.pstmt = null;
            }

            if (testcaseGenCon != null) {
                testcaseGenCon.close();
            }

        }
    }
}