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 statement = null;
		ResultSet rs = null;
		try {
			int resultNum = 0;
			String driverName = "com.mysql.jdbc.Driver";
			String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=GBK";
			String userName = "root";
			String password = "123456";
				
			/*try {
				Class.forName(driverName);	//加载MySql的驱动程序,jdbc4.0不再需要
			} catch (ClassNotFoundException e) {
				System.out.println("无法找到驱动类");
			}*/
			//连接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);";
			
			statement = con.createStatement();
			resultNum = statement.executeUpdate(strSql);
			System.out.println(resultNum+"条记录已经插入");
			
			//第一次执行Sql语句完,因为后面还要执行sql语句,所以不需要把statement和connection关闭
		
			//下面演示如何删除记录
			strSql = "delete from students where id='2'";//删除id=2的数据
			resultNum = statement.executeUpdate(strSql);
			System.out.println(resultNum+"条记录删除完毕");
			
			//下面演示如何更新记录
			strSql = "update students set major='中文' where id='1'";//更新id=2的数据
			resultNum = statement.executeUpdate(strSql);
			System.out.println(resultNum+"条记录更新完毕");
			
			
			//数据库现有记录数
			strSql = "select count(*) from students";//查询news表中的记录数量
			rs = statement.executeQuery(strSql);
			if(rs.next()){
				resultNum = rs.getInt(1);//取出第一列的值,注意:返回只有一列
				System.out.println("数据库存在"+resultNum+"条记录数据库");
			}
			
			//数据库中存在如下记录
			//strSql = "select * from news";
			strSql = "select stuno,name,birthdate,major from students";
			rs = statement.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();
		} finally {
			System.out.println("释放所有资源....");
			 try {
				
 if (con != null)
 con.close();//关闭连接
 con = null; //促使GC尽快回收资源
 } catch (Exception ex) {
 ex.printStackTrace();
 }
		}
	}
}