package com.jikexueyuan.jdbc;

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

public class JDBCTest {
    public static Connection getConnection(){
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");//注册驱动程序
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "123654");//获取数据库连接
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public static void insert(){
        Connection conn = getConnection();
        try {
            String sql = "INSERT INTO tbl_user(name,password,email)"+
                        "VALUES('Tom','123456','tom@gmail.com')";//定义sql字符串，用来保存sql语句
            Statement st = conn.createStatement(); //创建statement对象
            int count = st.executeUpdate(sql);//使用statement的executeUpdate方法来执行sql语句。
            System.out.println("向用户表中插入了："+count+"条记录");
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public static void update(){
        Connection conn = getConnection();
        try {
            String sql = "UPDATE tbl_user SET email='tom@126.com' WHERE name = 'Tom' ";//定义sql字符串，用来保存sql语句
            Statement st = conn.createStatement(); //创建statement对象
            int count = st.executeUpdate(sql);//使用statement的executeUpdate方法来执行sql语句。
            System.out.println("向用户表中更新了："+count+"条记录");
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public static void delete(){
        Connection conn = getConnection();
        try {
            String sql = "DELETE FROM tbl_user WHERE name='Tom' ";//定义sql字符串，用来保存sql语句
            Statement st = conn.createStatement(); //创建statement对象
            int count = st.executeUpdate(sql);//使用statement的executeUpdate方法来执行sql语句。
            System.out.println("从用户表中删除了："+count+"条记录");
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public static void main(String[] args) {
        insert();
        update();
        delete();
        
        
        
//      // TODO Auto-generated method stub
//      String sql = "SELECT * FROM tbl_user";
//      Connection conn = null; //当前数据库连接
//      Statement st = null;//向数据库发送sql语句
//      ResultSet rs = null;//结果集，封装了在数据库查询到的数据
//      
//      try {
//          
//          Class.forName("com.mysql.jdbc.Driver");//注册驱动程序
//          conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "123654");//获取数据库连接
//          
//          st = conn.createStatement();//创立statement对象
//          rs = st.executeQuery(sql);//使用statement的executeQuery方法发送sql语句，给方法返回ResultSet对象。
//          
//          while (rs.next()) {
//              System.out.print(rs.getInt("id")+" ");
//              System.out.print(rs.getString("name")+" ");
//              System.out.println(rs.getString("email")+" ");
//              System.out.println();
//          }
//          
//      } catch (Exception e) {
//          // TODO: handle exception
//          e.printStackTrace();
//      }
//      finally{
//          try {
//              rs.close();//关闭ResultSet结果集
//          } catch (Exception e2) {
//              // TODO: handle exception
//          }
//          try {
//              st.close();//关闭statement对象
//          } catch (Exception e2) {
//              // TODO: handle exception
//          }
//          try {
//              conn.close();//关闭数据库连接
//          } catch (Exception e2) {
//              // TODO: handle exception
//          }
//      }
    }
}

JDBC事务处理：

package com.jikexueyuan.jdbc;

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

public class TransitionTest {
    
    public static Connection getConnection(){//getConnection获取数据库连接
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "123654");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    
    public static void insterUserData(Connection conn) throws SQLException{//添加异常抛出代码，将SQLException抛出给上层调用方法进行处理
            String sql = "INSERT INTO tbl_user(id,name,password,email)"+
                                "VALUES(10,'Tom','123456','tom@gmail.com')";
            Statement st = conn.createStatement();
            int count = st.executeUpdate(sql);
            System.out.println("向用户表插入了"+count+"条记录");
            conn.close();
    }
    
    public static void insterAddressData(Connection conn)throws SQLException{
            String sql = "INSERT INTO tbl_address(id,city,country,user_id)"+
                                "VALUES(1,'shanghai','china','10')";
            Statement st = conn.createStatement();
            int count = st.executeUpdate(sql);
            System.out.println("向地址表插入了"+count+"条记录");
            conn.close();
    }
    
    
    
    public static void main(String[] args) {
            Connection conn =null;
        try {
            
            conn = getConnection();
            conn.setAutoCommit(false);//禁止事务的自动提交
            
            insterUserData(conn);
            insterAddressData(conn);
            
            conn.commit();//提交事务
        } catch (SQLException e) {
            System.out.println("捕获到sql异常");
            e.printStackTrace();
            try {
                conn.rollback();//回滚事务
                System.out.println("事务回滚成功");
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        finally{
            try{
                if(conn != null){
                    conn.close();
                }
            } catch (Exception e3) {
                // TODO: handle exception
                e3.printStackTrace();
            }
        }
        
    }

}