头歌JDBC

最新推荐文章于 2024-09-25 10:59:43 发布

青野不见

最新推荐文章于 2024-09-25 10:59:43 发布

阅读量882

点赞数

文章标签： java

本文链接：https://blog.csdn.net/idyou_/article/details/121622355

版权

JDBC

第一关

package jdbc;
import java.sql.*;
public class jdbcConn {
   public static void getConn() {
    /**********    Begin   **********/
    try {
        Class.forName("com.mysql.jdbc.Driver" );
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    /**********    End   **********/
    Connection conn = null;
    Statement statement = null;
    /**********    Begin   **********/
    String url = "jdbc:mysql://localhost:3306/";
    String user = "root";
    String password = "123123";
    try {
        conn = DriverManager.getConnection (url,user,password );
        statement = conn.createStatement();
        statement.executeUpdate("drop database if exists mysql_db");
        statement.executeUpdate("create database mysql_db");
        statement.executeUpdate("use mysql_db");
        String sql = "create table student(" +
                "id int not null, " +
                "name varchar(20), " +
                "sex varchar(4), " +
                "age int" +
                ")";
        statement.executeUpdate(sql);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    /**********    End   **********/
    finally {
        try {
            if(statement!=null)
                statement.close();
            if(conn!=null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
}

第二关

package jdbc;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class jdbcInsert {
   public static void insert(){
    /**********   Begin   **********/
    try {
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver" );
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    /**********   End   **********/
    Connection conn = null;
    PreparedStatement statement = null;
    /**********   Begin   **********/
    //连接并插入数据
    try{
        conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mysql_db","root","123123");
        PreparedStatement s = conn.prepareStatement("insert into student values(?,?,?,?)");
        s.setInt(1,1);s.setString(2,"张三");s.setString(3,"男");s.setInt(4,19);
        s.executeUpdate();
        s.setInt(1,2);s.setString(2,"李四");s.setString(3,"女");s.setInt(4,18);
        s.executeUpdate();
        s.setInt(1,3);s.setString(2,"王五");s.setString(3,"男");s.setInt(4,20);
        s.executeUpdate();
        s=conn.prepareStatement("select * from student");
        ResultSet r = s.executeQuery();
        while(r.next()){
            System.out.println(r.getString(1)+" "+r.getString(2)+" "+r.getString(3)+" "+r.getString(4));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    /**********   End   **********/
    finally {
      try {
            if (statement != null)
                statement.close();
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
}

第三关

package jdbc;
import java.sql.*;
public class jdbcTransaction {
    public static void transaction(){
    try {
        Class.forName("com.mysql.jdbc.Driver" );
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }
    Connection conn = null;
    PreparedStatement ps = null;
    /**********  Begin   **********/
    //连接数据库并开启事务
    String url = "jdbc:mysql://localhost:3306/mysql_db";
    try {
        conn = DriverManager.getConnection (url,"root","123123" );
        conn.setAutoCommit(false);//关闭自动提交开启事务
        ps = conn.prepareStatement("insert into student(id,name,sex,age) values(4,'赵六','女',21)");
        ps.executeUpdate();
        conn.commit();//提交事务
        ps = conn.prepareStatement("delete from student where id=2");
        ps.executeUpdate();
        ps = conn.prepareStatement("insert in student(id,name,sex,age) values(5,'钱七','男',18)");
        ps.executeUpdate();
        conn.commit();//提交事务
    } catch (SQLException e) {
        try {
            //事务回滚
            conn.rollback();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }
    /**********  End   **********/
    finally {
        try {
            if(ps!=null)
                ps.close();
            if (conn != null)
                conn.close();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }
}
}