Java高级特性 - JDBC（上）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

import java.sql.*;

public class jdbcConn {
    public static void getConn() {
        /**********    Begin   **********/
        try {
			//1.注册驱动
			Class.forName("com.mysql.jdbc.Driver");

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        /**********    End   **********/
        /**********    Begin   **********/
        Connection conn = null;
        Statement statement = null;
        //2.建立连接并创建数据库和表
		String url = "jdbc:mysql://127.0.0.1:3306/";
		String user = "root";
		String password = "123123";
		try{
			conn = DriverManager.getConnection(url,user,password);
			statement = conn.createStatement();
  			String sql1="drop database if exists mysql_db";
            String sql2="create database mysql_db";
            statement.executeUpdate(sql1);//执行sql语句
            statement.executeUpdate(sql2);
			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();
            }
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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();
            }
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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   **********/
        //连接数据库并开启事务
        try {
            conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/mysql_db","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("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();
            }
        }
    }
}