JDBC 复习笔记1

public class JDBC {

	public static void main(String[] args) {
		
		Connection cn = null;
		Statement sm = null;
		ResultSet rs = null;
		String url = "jdbc:mysql://localhost:3306/test";
		String user = "root";
		String password = "decamincow";

		try {
			
			//mysql对于接口实现的驱动进行加载
			Class.forName("com.mysql.jdbc.Driver");
			
			cn = DriverManager.getConnection(url, user, password);
			
			//设置事务提交
			cn.setAutoCommit(false);
			
			//设置事务隔离级别
			//ct.setTransactionIsolation(level);
			
			//sm用于向数据库发送要执行的SQL语句
			sm = cn.createStatement();
			
			//创建学生表
			sm.executeUpdate
					("CREATE TABLE student("
							+ "id int(10) NOT NULL AUTO_INCREMENT,   "
							+ "name varchar(20),  "
							+ "password varchar(20),  "
							+ "PRIMARY KEY (id)  )");
			
			//插入一个学生信息
			sm.executeUpdate
					("INSERT INTO student(name, password) VALUES('xiaoming','123');");
			
			//错误代码
//			int err = 1/0;
			
			//插入一个学生信息
			sm.executeUpdate
					("INSERT INTO student(name, password) VALUES('xiaohong','123');");
			
			//事务提交
			cn.commit();
			
			//查询，如果把错误注掉便能查到刚插入的数据
			rs = sm.executeQuery("SELECT * FROM student");
			
			//根据数据库显示行返回，如果到最后的空行返回false
			while(rs.next()){
				
				//根据列取
				System.out.println("name " + rs.getString(2) + "password" + rs.getString(3));
				
				//根据列名取
				System.out.println("name " + rs.getString("name") + "password" + rs.getString("password"));
			
			}
			
			
		} catch (Exception e) {
			e.printStackTrace();
			try {
				
				//事务回滚，这样xiaoming和xiaohong都不会出现
				//MD我发现建表的操作在mysql中不回滚，但是我觉的可以在这里执行删表操作
				cn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally{
			
			//关闭资源
			if (sm != null){
				try {
					sm.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
				sm = null;
			}
			if (cn != null){
				try {
					cn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				cn = null;
			}
		}
		
		
	}

}