import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class PreparedStatementTest {

 public static void main(String[] args) {
 Connection con = null;
 PreparedStatement pStatement = null;
 ResultSet rs = null;
 SimpleDateFormat hmFromat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");// 格式化日期

 // String driverName = "com.mysql.jdbc.Driver";
 String url = "jdbc:mysql://localhost:3306/test";
 /*连接test数据库,userUnicode和characterEncoding是正常插入中文所需要的,不过mysql较新版本无需在url中指定相应参数*/
 //String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8";
 
 String userName = "root";
 String password = "kfc@wanda";
 String driverName = "com.mysql.jdbc.Driver";

 // 数据库包含很多表 test.students , test.teachers, test.courses
 try {
 
 Class.forName(driverName);//jdbc4.0后无需载入驱动,不过为了保证兼容性一般保留 
 con = DriverManager.getConnection(url, userName, password);

 // 根据参数的插入数据
 String strSql = "insert into students(stuno,name,gender,birthdate,major,age) values(?,?,?,?,?,?)";
 pStatement = con.prepareStatement(strSql);
 pStatement.setString(1, "20150111");
 pStatement.setString(2, "周迅");
 pStatement.setString(3, "f");
 pStatement.setString(4, hmFromat.format(new Date()));
 pStatement.setString(5, "电气工程");
 pStatement.setInt(6, 18);
 pStatement.executeUpdate();
 pStatement.close();// 立即释放资源

 // 根据参数删除数据
 strSql = "delete from students where id > ?";
 pStatement = con.prepareStatement(strSql);
 pStatement.setInt(1, 3);
 int row = pStatement.executeUpdate();
 System.out.println("成功删除了" + row + "行数据!");
 pStatement.close();// 立即释放资源

 // 根据参数更新数据
 strSql = "update students set major = ? where Id = ?";
 pStatement = con.prepareStatement(strSql);
 pStatement.setString(1, "新闻");
 pStatement.setInt(2, 5);
 row = pStatement.executeUpdate();
 System.out.println("成功更新了" + row + "行数据!");
 pStatement.close();// 立即释放资源

 // 根据参数查询数据
 strSql = "select * from students where Id < ?";
 pStatement = con.prepareStatement(strSql);
 pStatement.setInt(1, 10);
 rs = pStatement.executeQuery();
 // 数据库中存在如下记录
 strSql = "select * from students";
 rs = pStatement.executeQuery(strSql);

 System.out.println("id\t编号 \t\t姓名\t性别\t出生年月日\t\t专业");
 while (rs.next()) {

 // 通过列的下标(index)取数据
 System.out.print(rs.getInt(1) + "\t");
 System.out.print(rs.getString(2) + " ");
 System.out.print(rs.getString(3) + "\t");
 System.out.print(rs.getString(4) + "\t ");
 System.out.print(rs.getDate(5) + "\t");// 这列有错,why?
 System.out.println(rs.getString(6));

 // 通过列名取数据(推荐大家都是用这种方式取数据)
 /*
 * System.out.println(rs.getInt("id"));
 * System.out.println(rs.getDate("birthdate"));
 * System.out.println(rs.getInt("age"));
 */

 }
 } catch (SQLException sqlE) {
 sqlE.printStackTrace();
 } catch (ClassNotFoundException e) {
 System.out.println("没有找到驱动类!");
 e.printStackTrace();
 }
 finally {
 if (rs != null)
 try {
 rs.close();
 rs = null;
 } catch (Exception e) {
 e.printStackTrace();
 }
 if (pStatement != null)
 try {
 pStatement.close();// 关闭语句
 pStatement = null;
 } catch (Exception e) {
 e.printStackTrace();
 }
 if (con != null) {
 try {
 con.close();// 关闭连接
 con = null;
 } catch (Exception e) {
 e.printStackTrace();
 }
 }
 }
 }

}