hooyantsing's Blog

第45次课程

字数统计: 891阅读时长: 4 min
2019/07/23

源辰74班

第45次课程

07.23.2019

内容

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
package com.yc.jdbc;

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

public class JdbcTest {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        JdbcTest jt = new JdbcTest();
        jt.set();
        //jt.saveDept();

    }
    
    //增加一条表记录
    private void saveDept() throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //创建连接
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "scott";
        String password = "tiger";
        Connection conn = DriverManager.getConnection(url, user, password);
        
        //新增一个部门
        String sql = "insert into dept values(?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, 52);
        ps.setString(2, "行政部");
        ps.setString(3, "广州");
        //executeUpdate() 用于执行 增删改 返回的结果表示影响的行数
        ps.executeUpdate();
        System.out.println("部门添加成功");
        conn.close();
    }
    
    //删除一条表记录
    private void delete() throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //创建连接
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "scott";
        String password = "tiger";
        Connection conn = DriverManager.getConnection(url, user, password);
        
        //删除一个部门
        String sql = "delete dept where deptno = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, 50);
        //executeUpdate() 用于执行 增删改 返回的结果表示影响的行数
        ps.executeUpdate();
        System.out.println("部门删除成功");
        conn.close();
    }
    
    //修改一条表记录
    private void set() throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //创建连接
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "scott";
        String password = "tiger";
        Connection conn = DriverManager.getConnection(url, user, password);
        
        //修改一个部门
        String sql = "update dept set DNAME = ?,LOC = ? where DEPTNO = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, "财务部");
        ps.setString(2, "上海");
        ps.setInt(3, 51);
        //executeUpdate() 用于执行 增删改 返回的结果表示影响的行数
        ps.executeUpdate();
        System.out.println("部门修改成功");
        conn.close();
    }
    
    //查询表记录(预编译写法 推荐)
    private void selectEmpByName(String ename) throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //创建连接
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "scott";
        String password = "tiger";
        Connection conn = DriverManager.getConnection(url, user, password);
        
        String sql = "select * from emp where ename like ?";
        //创建预编译sql语句对象,使用?(占位符)
        PreparedStatement ps = conn.prepareStatement(sql);
        //设置参数,setXXX方法用于设置参数值,XXX表示类型,参数编号从1开始
        ps.setString(1, "%"+ename+"%");
        ResultSet rs = ps.executeQuery();
        System.out.println("姓名\t职位\t工资");
        //next() 返回游标是否有指向一条记录,如果有则返回true
        while(rs.next()) {
            //
            String name = rs.getString("ENAME");
            String job = rs.getString("JOB");
            float sal = rs.getFloat("SAL");
            System.out.printf("%s\t%s\t%s\n",name,job,sal);
        }
        conn.close();
    }

    //查询表记录(直接写法 不安全 SQL注入风险)
    private void selectAllDept() throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //创建连接
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "scott";
        String password = "tiger";
        Connection conn = DriverManager.getConnection(url, user, password);
        //创建语句对象
        Statement stat = conn.createStatement();
        //执行语句对象
        String sql = "select * from dept";
        //获取结果集 ResultSet
        ResultSet rs = stat.executeQuery(sql);
        //next() 返回游标是否有指向一条记录,如果有则返回true
        while(rs.next()) {
            //ResultSet 对象提供了大量get XXX 方法,XXX表示数据的类型
            int deptno = rs.getInt(1);
            String dname = rs.getString(2);
            String loc = rs.getString(3);
            System.out.printf("部门编号:%s  部门名称:%s  地址:%s\n",deptno,dname,loc);
        }
        //关闭连接
        rs.close();
        stat.close();
        conn.close();
    }
}
CATALOG