import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class StatementTest {
 public static void main(String[] args) {
 Connection con = null;
 Statement stmt = null;
 ResultSet rs = null;
 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 = "123456";
 String driverName = "com.mysql.jdbc.Driver";
 
 try {
 int resultNum = 0;
 

 Class.forName(driverName);//jdbc4.0后无需载入驱动,不过为了保证兼容性一般保留
 // 连接test数据库,userUnicode和characterEncoding是正常插入中文所需要的,用户名为root,密码为123456
 con = DriverManager.getConnection(url, userName, password);

 // 演示如何插入记录
 String strSql = "insert into students(stuno,name,gender,birthdate,major,age) values('201010005', '周杰伦', 'm',now(),'音乐',21);";

 stmt = con.createStatement();
 resultNum = stmt.executeUpdate(strSql);
 System.out.println(resultNum + "条记录已经插入");

 // 第一次执行Sql语句完,因为后面还要执行sql语句,所以不需要把statement和connection关闭

 // 下面演示如何删除记录
 strSql = "delete from students where id='2'";// 删除id=2的数据
 resultNum = stmt.executeUpdate(strSql);
 System.out.println(resultNum + "条记录删除完毕");

 // 下面演示如何更新记录
 strSql = "update students set major='中文' where id='1'";// 更新id=2的数据
 resultNum = stmt.executeUpdate(strSql);
 System.out.println(resultNum + "条记录更新完毕");

 // 数据库现有记录数
 strSql = "select count(*) from students";// 查询news表中的记录数量
 rs = stmt.executeQuery(strSql);
 if (rs.next()) {
 resultNum = rs.getInt(1);// 取出第一列的值,注意:返回只有一列
 System.out.println("数据库存在" + resultNum + "条记录数据库");
 }
 // 这里应该加上rs.close,否则会资源泄露,为了演示方便不加

 // 数据库中存在如下记录
 // strSql = "select * from news";
 strSql = "select stuno,name,birthdate,major from students";
 rs = stmt.executeQuery(strSql);
 System.out.println("学号\t\t姓名\t出生日期\t\t专业");
 while (rs.next()) {
 System.out.print(rs.getString("stuno") + "\t");// 根据列名取值
 System.out.print(rs.getString(2) + "\t");// 根据列的顺序取值,如name在上述Sql语句中的第2列
 System.out.print(rs.getDate("birthdate") + "\t");// 根据列名取值
 System.out.print(rs.getString(4) + "\t\n");// 根据列的顺序取值,如major在第4列
 }

 } catch (SQLException sqlE) {
 sqlE.printStackTrace();
 } catch (ClassNotFoundException e) {
 System.out.println("没有找到驱动类!");
 e.printStackTrace();
 } finally {
 System.out.println("释放所有资源....");
 if (rs != null) {
 try {
 rs.close();
 rs = null;
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }

 try {
 if (stmt != null) {
 stmt.close();// 关闭语句
 stmt = null;
 }
 } catch (Exception ex) {
 ex.printStackTrace();
 }
 try {
 if (con != null) {
 con.close();// 关闭连接
 con = null;
 }
 } catch (Exception ex) {
 ex.printStackTrace();
 }
 }
 }
}