一。jbdc的常用API
1.Connection:数据库的链接对象
2.statement:数据库sql执行对象
3.preparedStatment：sql的预编译处理对象，是statement子接口
4.resultset：返回查询的结果集
二。jdbc开发步骤
1.在项目中加入驱动jar包
2.写jdbc链接代码
注意：日期对象的处理。从结果集中获取时间是用getTimestamp(),得到的是Timestamp对象（时间戳）
Timestamp是util.Date的子类。他们之间的互相转换是：
util.Date = Timestamp直接转换
Timestamp = new TimeStamp(util.Date.getTime());
三。工厂模式
1.工厂类，专门用来生产某一个对象的实例
四。preparedStatment 预编译sql命令接口
1.会对sql语句进行编译检查，可以用参数占位符的方式编写sql语句
2.作用：比普通statement接口执行效率更高。可以防止sql注入的侵入
SQL实例：
1 drop table user_info;
2 drop table group_info;
3 drop table contacts_info;
4
5 select * from user_info
6 select * from group_info
7 select * from contacts_info
8
9 delete from user_info;
10 delete from group_info;
11
12
13 --创建用户信息表
14 create table user_info(
15 user_id int identity(1,1) primary key,
16 user_name nvarchar(30) unique not null,
17 user_password nvarchar(30) not null
18 )
19
20 --创建联系人群组信息表
21 create table group_info(
22 group_id int identity(1,1) primary key,
23 group_name nvarchar(30),
24 group_state nchar(3) default '可修改',
25 check(group_state in ('可修改','不可改')),
26 user_id int,
27 foreign key (user_id) references user_info(user_id)
28 )
29
30 --创建联系人信息表
31 create table contacts_info(
32 con_id int identity(1,1) primary key,
33 con_name nvarchar(30) not null,
34 con_sex nchar(1) default '男',
35 check(con_sex in ('男','女')),
36 con_age int,
37 con_cellphone nvarchar(30),
38 con_telephone nvarchar(30),
39 con_birth datetime,
40 con_email nvarchar(30),
41 con_static nchar(3) default '未删除',
42 check(con_static in ('未删除','已删除')),
43 group_id int,
44 foreign key (group_id) references group_info(group_id)
45 )
java实例1：创建jdbc工厂类
jdbc.properties
jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
jdbc.username=C##java06
jdbc.password=java123
JDBCFactory.java类
1 package com.demo1207;
2
3 import java.io.IOException;
4 import java.io.InputStream;
5 import java.sql.Connection;
6 import java.sql.DriverManager;
7 import java.sql.ResultSet;
8 import java.sql.SQLException;
9 import java.sql.Statement;
10 import java.util.Properties;
11
12 public class JDBCFactory {
13 // private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
14 // private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
15 // private static final String USERNAME = "C##java06";
16 // private static final String PASSWORD = "java123";
17
18 static String DRIVER;
19 static String URL;
20 static String USERNAME;
21 static String PASSWORD;
22 static{
23 //只会在类第一次加载时被执行一次，适合做资源文件的读取
24 //加载数据库配置文件资源
25 Properties pro = new Properties();
26 //把资源读取成字节输入流
27 InputStream is = JDBCFactory.class.getResourceAsStream("jdbc.properties");
28
29 try {
30 //通过资源对象加载字节输入流
31 pro.load(is);
32 //资源对象通过key来获取对应的文件中的值，注意：静态代码块只能使用静态属性
33 DRIVER = pro.getProperty("jdbc.driver");
34 URL = pro.getProperty("jdbc.url");
35 USERNAME = pro.getProperty("jdbc.username");
36 PASSWORD = pro.getProperty("jdbc.password");
37 } catch (IOException e) {
38 // TODO Auto-generated catch block
39 e.printStackTrace();
40 }
41 }
42
43 /**
44 * 获取数据库链接
45 * @return 如果有异常则会返回null
46 */
47 public static Connection getConn(){
48 Connection conn = null;
49 try {
50 Class.forName(DRIVER);
51 conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
52 } catch (Exception e) {
53 e.printStackTrace();
54 }
55 return conn;
56 }
57
58 public static void closeAll(Connection conn,Statement st,ResultSet rs){
59 if(conn!=null){
60 try {
61 conn.close();
62 } catch (SQLException e) {
63 e.printStackTrace();
64 }
65 }
66 if(st!=null){
67 try {
68 st.close();
69 } catch (SQLException e) {
70 e.printStackTrace();
71 }
72 }
73 if(rs!=null){
74 try {
75 rs.close();
76 } catch (SQLException e) {
77 // TODO Auto-generated catch block
78 e.printStackTrace();
79 }
80 }
81 }
82 }
**java实例2:**调用工厂类
在java类中写一个Student对象，用来封装学员信息
查询学员信息表，将结果集封装到List
提示：学员对象的时间字段用util.Date. 每个属性都要封装
Student.java
1 package com.demo1207;
2
3 import java.util.Date;
4
5 public class Student {
6 private int student_id;
7 private String student_name;
8 private String student_sex;
9 private int student_age;
10 private int class_id;
11 private Date birthday;
12 public int getStudent_id() {
13 return student_id;
14 }
15 public void setStudent_id(int student_id) {
16 this.student_id = student_id;
17 }
18 public String getStudent_name() {
19 return student_name;
20 }
21 public void setStudent_name(String student_name) {
22 this.student_name = student_name;
23 }
24 public String getStudent_sex() {
25 return student_sex;
26 }
27 public void setStudent_sex(String student_sex) {
28 this.student_sex = student_sex;
29 }
30 public int getStudent_age() {
31 return student_age;
32 }
33 public void setStudent_age(int student_age) {
34 this.student_age = student_age;
35 }
36 public int getClass_id() {
37 return class_id;
38 }
39 public void setClass_id(int class_id) {
40 this.class_id = class_id;
41 }
42 public Date getBirthday() {
43 return birthday;
44 }
45 public void setBirthday(Date birthday) {
46 this.birthday = birthday;
47 }
48
49
50 }
JdbcTest.java
1 package com.demo1207;
2
3 import java.sql.Connection;
4 import java.sql.DriverManager;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.sql.Statement;
8 import java.util.ArrayList;
9 import java.util.List;
10
11 public class JdbcTest {
12 public static void main(String[] args) {
13 Connection conn = null;
14 Statement st = null;
15 ResultSet rs = null;
16 try {
17 conn = JDBCFactory.getConn();
18 System.out.println(conn);
19
20 //处理sql命令的对象
21 st = conn.createStatement();
22
23 String sql = "insert into student_info values(sq_student.nextval,'叶挺',1,22,1,sysdate)";
24 //st来执行sql语句，注意executeUpdate是执行增删改的语句
25 st.executeUpdate(sql);
26
27 //执行查询业务
28 String sql2 = "select * from student_info";
29 rs = st.executeQuery(sql2);
30 List<Student> list = new ArrayList<>();
31 while(rs.next()){
32 Student stu = new Student();
33 stu.setStudent_id(rs.getInt(1));
34 stu.setStudent_name(rs.getString(2));
35 stu.setStudent_sex(rs.getString(3));
36 stu.setStudent_age(rs.getInt(4));
37 stu.setClass_id(rs.getInt(5));
38 stu.setBirthday(rs.getTimestamp(6));
39 list.add(stu);
40 System.out.print(rs.getInt(1)+"\t");
41 System.out.print(rs.getString("student_name")+"\t");
42 System.out.print(rs.getString(3)+"\t");
43 System.out.print(rs.getInt(4)+"\t");
44 System.out.print(rs.getInt(5)+"\t");
45 System.out.println(rs.getTimestamp(6));
46 }
47 } catch (Exception e) {
48 e.printStackTrace();
49 } finally {
50 JDBCFactory.closeAll(conn, st, rs);
51 }
52 }
53 }
java实例3：调用jdbc工厂类来验证登录
PreparedDemo.java
1 package com.demo1207;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6
7 public class PreparedDemo {
8 public static void main(String[] args) {
9 Connection conn = null;
10 PreparedStatement ps = null;
11 ResultSet rs = null;
12
13 try {
14 conn = JDBCFactory.getConn();
15 String sql = "select * from user_info where username=? and pass_word=?";
16 ps = conn.prepareStatement(sql);
17 //将参数占位符赋值
18 ps.setString(1, "张三");
19 ps.setString(2, "123456");
20
21 //执行sql 和statement的执行方法一样
22 rs = ps.executeQuery();
23 if(rs.next()){
24 System.out.println(rs.getString(2)+"登录成功");
25 }else{
26 System.out.println("登录失败");
27 }
28 } catch (Exception e) {
29 e.printStackTrace();
30 } finally {
31 JDBCFactory.closeAll(conn, ps, rs);
32 }
33 }
34 }
java实例4：调用工厂类进行增删改查
1 package com.demo1207;
2
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.Timestamp;
7 import java.util.Date;
8
9 public class CRUDDemo {
10 Connection conn;
11 PreparedStatement ps;
12 ResultSet rs;
13
14 public void create(){
15 try {
16 conn = JDBCFactory.getConn();
17
18 String sql = "insert into student_info values(sq_student.nextval,?,?,?,?,?)";
19 ps = conn.prepareStatement(sql);
20
21 ps.setString(1, "田甜");
22 ps.setString(2, "2");
23 ps.setInt(3, 22);
24 ps.setInt(4, 1);
25 ps.setTimestamp(5, new Timestamp(new Date().getTime()));
26 ps.executeUpdate();
27 System.out.println("新增成功");
28 } catch (Exception e) {
29 e.printStackTrace();
30 } finally {
31 JDBCFactory.closeAll(conn, ps, rs);
32 }
33 }
34
35 public void delete(){
36 try {
37 conn = JDBCFactory.getConn();
38
39 String sql = "delete from student_info where student_id=?";
40 ps = conn.prepareStatement(sql);
41
42 ps.setInt(1, 1);
43 ps.executeUpdate();
44 System.out.println("删除成功");
45 } catch (Exception e) {
46 e.printStackTrace();
47 } finally {
48 JDBCFactory.closeAll(conn, ps, rs);
49 }
50 }
51
52 public void update(){
53 try {
54 conn = JDBCFactory.getConn();
55
56 String sql = "update student_info set student_name=?,student_sex=?,student_age=?,class_id=?,birthday=? where student_id=?";
57 ps = conn.prepareStatement(sql);
58
59 ps.setString(1, "哈哈");
60 ps.setString(2, "2");
61 ps.setInt(3, 28);
62 ps.setInt(4, 1);
63 ps.setTimestamp(5, new Timestamp(new Date().getTime()));
64 ps.setInt(6, 2);
65 ps.executeUpdate();
66 System.out.println("修改成功");
67 } catch (Exception e) {
68 e.printStackTrace();
69 } finally {
70 JDBCFactory.closeAll(conn, ps, rs);
71 }
72 }
73
74 public void query(){
75 try {
76 conn = JDBCFactory.getConn();
77
78 String sql = "select * from student_info";
79 ps = conn.prepareStatement(sql);
80
81 rs = ps.executeQuery();
82 while(rs.next()){
83 System.out.println();
84 }
85 } catch (Exception e) {
86 e.printStackTrace();
87 } finally {
88 JDBCFactory.closeAll(conn, ps, rs);
89 }
90 }
91 }
作业：
一。用户管理
1.用户注册：要求用户名不能重复
2.用户登录
二。联系人组群管理
1.用户新建联系人组群：每个用户注册时都会默认新建一个名字叫“我的联系人”这么一个组群，该组群不能修改，
每个用户可以新建n个联系人组群。
2.用户修改联系组群：修改组群名字
3.用户删除组群：删除组群后，将该组的所有联系人移到默认组群“我的联系人”
三。联系人管理
1.用户新建联系人：需要指定联系人到哪个组群。联系人信息（姓名，年龄，性别，移动电话，固定电话，生日，邮箱）
2.用户修改联系人：可以修改联系人所有信息，包括组群
3.用户删除联系人：
4.查询联系人：
a.按姓名模糊查询
b.按组群查询
c.按电话号码模糊查询
