PreparedStatementの使い方の例

SQLに指定する値を動的に設定したい場合などに使えるPreparedStatementの例。
WHERE文の指定に変数を使うときなどに使おう。

package jdbc;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.mysql.jdbc.Connection;


public class MySQLCount {
	private static String jdbc = null;
	private static String db = null;
	private static String host = null;
	private static String user = null;
	private static String pass = null;
	private static String driver = null;
	private static String sql = null;
	
	public static void main(String[] args){
		Properties prop = new Properties();
		FileInputStream fis = null;
		try {
			fis = new FileInputStream("properties.txt");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			System.err.println("プロパティファイルが見つかりません");
		}
		
		try {
			prop.load(fis);
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("プロパティファイルを開けません");
		}
		
		//プロパティの読み込み
		jdbc = prop.getProperty("jdbc");
		db = prop.getProperty("db");
		host = prop.getProperty("host");
		user = prop.getProperty("user");
		pass = prop.getProperty("pass");
		//sql = prop.getProperty("sql");
		
		//ドライバの作成
		driver = "jdbc:"+jdbc+"://"+host+"/"+db+"?user="+user+"&"+"password="+pass;
		Connection con = null;
		try{
			con = (Connection) DriverManager.getConnection(driver);
			//Statement stmt = (Statement) con.createStatement();
			/**
			ResultSet rs = stmt.executeQuery("select COUNT(*) cnt FROM test1");
			rs.next();
			System.out.println(rs.getInt("cnt"));
			*/
			
			/**
			//insert文を発行
			String sql = "INSERT INTO test1(id,title,author,sentence) VALUES(4,\'ダイの大冒険\',\'忘れた\',\'ポップ~！！\')";
			//executeQuery()メソッドを使うとエラーが発生する
			stmt.execute(sql);
			//stmt.executeQuery(sql);
			*/
			
			//PreparedStatementの使い方
			String sql = "SELECT * FROM test1 WHERE id = ?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1,2);
			ResultSet rs = stmt.executeQuery();
			rs.next();
			System.out.println(rs.getString("title"));
			
		}catch(SQLException ex){
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
		}finally{
			if(con != null){
				try{
					con.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
			}
		}
		
	}
}