Java PreparedStatement.setString方法代碼示例

import java.sql.PreparedStatement; //導入方法依賴的package包/類
private int insert(Connection con,List<Map<String, String>> list) throws SQLException {
	PreparedStatement ps;
	String sql = "insert into goods (id,name ,good_type,good_img_url,good_created ,good_desc, price ) values(?,? ,?,?,? ,?, ?)";
	ps = con.prepareStatement(sql);
	for (Map<String, String> map : list) {
		ps.setLong(1, Long.parseLong(map.get("id")));
		ps.setString(2, (String) map.get("name"));
		ps.setShort(3, Short.parseShort(map.get("good_type")));
		ps.setString(4, (String) map.get("good_img_url"));
		ps.setString(5, (String) map.get("good_created"));
		ps.setString(6, (String) map.get("good_desc"));
		ps.setDouble(7, Double.parseDouble(map.get("price")));
		ps.addBatch();
	}
	ps.executeBatch();
	return list.size();
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
/**
 * Tests fix for BUG#1658
 * 
 * @throws Exception
 *             if the fix for parameter bounds checking doesn't work.
 */
public void testParameterBoundsCheck() throws Exception {
    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testParameterBoundsCheck");
        this.stmt.executeUpdate("CREATE TABLE testParameterBoundsCheck(f1 int, f2 int, f3 int, f4 int, f5 int)");

        PreparedStatement _pstmt = this.conn.prepareStatement("UPDATE testParameterBoundsCheck SET f1=?, f2=?,f3=?,f4=? WHERE f5=?");

        _pstmt.setString(1, "");
        _pstmt.setString(2, "");

        try {
            _pstmt.setString(25, "");
        } catch (SQLException sqlEx) {
            assertTrue(SQLError.SQL_STATE_ILLEGAL_ARGUMENT.equals(sqlEx.getSQLState()));
        }
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testParameterBoundsCheck");
    }
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
/**
 * Fills in the given fields in the queue into the given prepared statement
 * @param ps
 * @param firstField
 * @return
 * @throws SQLException
 */
public int fillInWhereClause(PreparedStatement ps, int firstField) throws SQLException{
	//insert all values in the prepared statement in the order
    //in which the values had been put in the queue
    for (Map.Entry<String, FieldType> entry : searchValues){
        switch(entry.getValue()) {
            case STRING:
                ps.setString(firstField, entry.getKey());
                break;
            case DATE:
                ps.setTimestamp(firstField, new Timestamp(Long.parseLong(entry.getKey())));
                break;
            case LONG:
                ps.setLong(firstField, Long.parseLong(entry.getKey()));
                break;
            case DOUBLE:
                ps.setDouble(firstField, Double.parseDouble(entry.getKey()));
                break;
            case UUID:
                ps.setObject(firstField, UUID.fromString(entry.getKey()));
                break;
            case BOOLEAN:
            	ps.setBoolean(firstField, Boolean.valueOf(entry.getKey()));
            	break;
        }
        firstField++;
    }
    return firstField;
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
public static void insertFuenteFinanciamietno(int numero_fila,int anho,int id,String nombre, String descripcion){
 	 Connection conect=conectar();
 String query = " insert into fuente_financiamiento (numero_fila,anho,id,nombre,descripcion)"
	        + " values (?,?,?,?,?)";
try {
	
	PreparedStatement preparedStmt;
	preparedStmt = conect.prepareStatement(query);
	preparedStmt.setInt (1, numero_fila);
	preparedStmt.setInt (2,anho);
	preparedStmt.setInt (3, id);
	preparedStmt.setString (4, nombre);
	preparedStmt.setString (5, descripcion);
    preparedStmt.execute();
    conect.close();
} catch (SQLException e) {e.printStackTrace();}
 }
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
public static void insertFuenteVerificacion(FuenteVerificacion fuenteVerificacion, String usuarioResponsable){
	try {
		Connection conn=ConnectionConfiguration.conectar();
	   	
		String query = " insert into fuente_verificacion (id,nombre,descripcion,abrev,uri,usuario_responsable)"
		+ " values (?, ?, ?, ?, ?, ?)";
		
		PreparedStatement insert = conn.prepareStatement(query);
		insert.setInt (1, fuenteVerificacion.getId());
		insert.setString (2, fuenteVerificacion.getNombre());
		insert.setString (3, fuenteVerificacion.getDescripcion());
		insert.setString (4, fuenteVerificacion.getAbrev());
		insert.setString (5, fuenteVerificacion.getUri());
		insert.setString (6, usuarioResponsable);
		
		insert.execute();
		   
		conn.close();
	} catch (SQLException e) {e.printStackTrace();}
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
@Override
public int addBook(Book book) {
	// TODO Auto-generated method stub
	int rows=0;
	String INSERT_BOOK="insert into book values(?,?,?,?,?,?)";
	try {
		Connection connection=dataSource.getConnection();
		PreparedStatement ps= connection.prepareStatement(INSERT_BOOK);
		ps.setString(1,book.getBookName());
		ps.setLong(2,book.getISBN());
		ps.setString(3,book.getPublication());
		ps.setInt(4,book.getPrice());
		ps.setString(5,book.getDescription());
		ps.setString(6,book.getAuthor());
		rows=ps.executeUpdate();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return rows;
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
@Override
public int save(Pet pet)
{
	PreparedStatement stat = null;
	int result = 0 ;
	try
	{
		String sql = "insert into pet (id, name) values (?, ?)";
		stat = conn.prepareStatement(sql);
		stat.setString(1, master.getId());
		stat.setString(2, master.getName());
		result = stat.executeUpdate();

	}
	catch (SQLException  e) 
	{
		LOGGER.catching(e);
	}
	finally
	{
		DBHelper.closeResultSet(rs);
		DBHelper.closeStatement(stat);
	}
	return result;
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
public static int createPet(int itemid, byte level, int closeness, int fullness) {
    try {
        PreparedStatement ps = DatabaseConnection.getConnection().prepareStatement("INSERT INTO pets (name, level, closeness, fullness, summoned) VALUES (?, ?, ?, ?, 0)", Statement.RETURN_GENERATED_KEYS);
        ps.setString(1, MapleItemInformationProvider.getInstance().getName(itemid));
        ps.setByte(2, level);
        ps.setInt(3, closeness);
        ps.setInt(4, fullness);
        ps.executeUpdate();
        ResultSet rs = ps.getGeneratedKeys();
        int ret = -1;
        if (rs.next()) {
            ret = rs.getInt(1);
            rs.close();
            ps.close();
        }
        return ret;
    } catch (SQLException e) {
        return -1;
    }
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
/**
 * Tests fix for BUG#4010 -- GBK encoding getting escaped doubly when
 * database default character set is GBK. Requires version older than 4.1.0
 * and server set to default character set of 'gbk' to run.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug4010() throws Exception {
    if (!versionMeetsMinimum(4, 1)) {
        if ("GBK".equalsIgnoreCase(getMysqlVariable("character_set"))) {
            String origString = "\u603d";
            Properties props = new Properties();
            props.put("useUnicode", "true");
            props.put("characterEncoding", "GBK");

            Connection unicodeConn = getConnectionWithProps(props);
            Statement unicodeStmt = unicodeConn.createStatement();
            PreparedStatement unicodePstmt = unicodeConn.prepareStatement("INSERT INTO testBug4010 VALUES (?)");

            try {
                unicodeStmt.executeUpdate("DROP TABLE IF EXISTS testBug4010");
                unicodeStmt.executeUpdate("CREATE TABLE testBug4010 (field1 varchar(10))");

                unicodePstmt.setString(1, origString);
                unicodePstmt.executeUpdate();

                this.rs = unicodeStmt.executeQuery("SELECT * FROM testBug4010");
                assertTrue(this.rs.next());

                String stringFromDb = this.rs.getString(1);
                assertTrue("Retrieved string != sent string", origString.equals(stringFromDb));
            } finally {
                unicodeStmt.executeUpdate("DROP TABLE IF EXISTS testBug4010");
                unicodeStmt.close();
                unicodePstmt.close();
                unicodeConn.close();
            }
        } else {
            System.err.println("WARN: Test not valid for servers not running GBK encoding");
        }
    } else {
        System.err.println("WARN: Test not valid for MySQL version > 4.1.0, skipping");
    }
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
/**
*Method update game's review 
*
*@param testoRecensione game's review
*@param utente user who votes
*@param gioco gioco review
*@throws SQLException if no database connection is found or another error occurs
*/
@Override
public void updateReview(String testoRecensione, Utente utente, Gioco gioco) throws SQLException{
  Connection connection = DB.openConnection();
  PreparedStatement ps = connection.prepareStatement(UPDATE_REVIEW);
  ps.setString(1, testoRecensione);
  ps.setInt(2, utente.getId());
  ps.setInt(3, gioco.getId());
  ps.executeUpdate();
  ps.close();
  connection.close();
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
/**
 * Tests for BUG#5136, GEOMETRY types getting corrupted, turns out to be a
 * server bug.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug5136() throws Exception {
    if (!this.DISABLED_testBug5136) {
        PreparedStatement toGeom = this.conn.prepareStatement("select GeomFromText(?)");
        PreparedStatement toText = this.conn.prepareStatement("select AsText(?)");

        String inText = "POINT(146.67596278 -36.54368233)";

        // First assert that the problem is not at the server end
        this.rs = this.stmt.executeQuery("select AsText(GeomFromText('" + inText + "'))");
        this.rs.next();

        String outText = this.rs.getString(1);
        this.rs.close();
        assertTrue("Server side only\n In: " + inText + "\nOut: " + outText, inText.equals(outText));

        // Now bring a binary geometry object to the client and send it back
        toGeom.setString(1, inText);
        this.rs = toGeom.executeQuery();
        this.rs.next();

        // Return a binary geometry object from the WKT
        Object geom = this.rs.getObject(1);
        this.rs.close();
        toText.setObject(1, geom);
        this.rs = toText.executeQuery();
        this.rs.next();

        // Return WKT from the binary geometry
        outText = this.rs.getString(1);
        this.rs.close();
        assertTrue("Server to client and back\n In: " + inText + "\nOut: " + outText, inText.equals(outText));
    }
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
@Override
public Collection<MessageId> getMessagesToShare(
		Connection txn, ClientId c) throws DbException {
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		String sql = "SELECT m.messageId FROM messages AS m"
				+ " JOIN messageDependencies AS d"
				+ " ON m.messageId = d.dependencyId"
				+ " JOIN messages AS m1"
				+ " ON d.messageId = m1.messageId"
				+ " JOIN groups AS g"
				+ " ON m.groupId = g.groupId"
				+ " WHERE m.shared = FALSE AND m1.shared = TRUE"
				+ " AND g.clientId = ?";
		ps = txn.prepareStatement(sql);
		ps.setString(1, c.getString());
		rs = ps.executeQuery();
		List<MessageId> ids = new ArrayList<MessageId>();
		while (rs.next()) ids.add(new MessageId(rs.getBytes(1)));
		rs.close();
		ps.close();
		return ids;
	} catch (SQLException e) {
		tryToClose(rs);
		tryToClose(ps);
		throw new DbException(e);
	}
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
/**
 * @param args
 */
public static void main(String[] args) {
    try {
        Class.forName("org.sqlite.JDBC");
        Connection conn =
                DriverManager.getConnection("jdbc:sqlite:D:/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();
    } catch (Exception e) {
        e.printStackTrace();
    }
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
private void addBatchItems(Statement statement, PreparedStatement pStmt, String tableName, int i) throws SQLException {
    pStmt.setString(1, "ps_batch_" + i);
    pStmt.setString(2, "ps_batch_" + i);
    pStmt.addBatch();

    statement.addBatch("INSERT INTO " + tableName + " (strdata1, strdata2) VALUES (\"s_batch_" + i + "\",\"s_batch_" + i + "\")");
}
 

import java.sql.PreparedStatement; //導入方法依賴的package包/類
@Override
public void setUpdateStatementParameters(PreparedStatement preparedStatement, Map<Long, CloudTarget> transformedData)
    throws SQLException {
    for (Map.Entry<Long, CloudTarget> entry : transformedData.entrySet()) {
        CloudTarget cloudTarget = entry.getValue();
        preparedStatement.setString(1, cloudTarget.getOrg());
        preparedStatement.setString(2, cloudTarget.getSpace());
        preparedStatement.setLong(3, entry.getKey());
        preparedStatement.addBatch();
        logger.debug(String.format("Executed update for row ID: '%s' , TARGET_ORG: '%s' , TARGET_SPACE: '%s'", entry.getKey(),
            cloudTarget.getOrg(), cloudTarget.getSpace()));
    }
}