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

@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);
	}
}
 

/**
 * 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();
}
 

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);
}
 

@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);
    }
    
}
 

/**
   * 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;
  }
 

/**
 * <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);
    }
}
 

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);
    }
}
 

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.");
}
}
 

/**
 * 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());
	}
}
 

/**
 * 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);
}
 

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();
}
 

/**
 * 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);
    }

}
 

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;
	}
}
 

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
		}
	}
}
 

/**
 * @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;
}
 

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();
    }
}
 

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;
  }
}
 

/**
 * 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();
}
 

private PreparedStatement getStatement(Connection conn, String sql,
		int type, int concurrency) throws Exception {
	return conn.prepareStatement(sql, type, concurrency);
}
 

/**
 * 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();
            }

        }
    }
}