1、原生jdbc连接oracle数据库简单介绍

一。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 1 drop table user_info;
2 2 drop table group_info;
3 3 drop table contacts_info;
4 4 
5 5 select * from user_info
6 6 select * from group_info
7 7 select * from contacts_info
8 8 
9 9 delete from user_info;
1010 delete from group_info;
1111 
1212 
1313 --创建用户信息表
1414 create table user_info(
1515     user_id int identity(1,1) primary key,
1616     user_name nvarchar(30) unique not null,
1717     user_password nvarchar(30) not null
1818     )
1919     
2020 --创建联系人群组信息表
2121 create table group_info(
2222     group_id int identity(1,1) primary key,
2323     group_name nvarchar(30),
2424     group_state nchar(3) default '可修改',
2525     check(group_state in ('可修改','不可改')),
2626     user_id int,
2727     foreign key (user_id) references user_info(user_id)
2828     )
2929     
3030 --创建联系人信息表
3131 create table contacts_info(
3232     con_id int identity(1,1) primary key,
3333     con_name nvarchar(30) not null,
3434     con_sex nchar(1) default '男',
3535     check(con_sex in ('男','女')),
3636     con_age int,
3737     con_cellphone nvarchar(30),
3838     con_telephone nvarchar(30),
3939     con_birth datetime,
4040     con_email nvarchar(30),
4141     con_static nchar(3) default '未删除',
4242     check(con_static in ('未删除','已删除')),
4343     group_id int,
4444     foreign key (group_id) references group_info(group_id)
4545     )

java实例1：创建jdbc工厂类

　　jdbc.properties

1jdbc.driver=oracle.jdbc.driver.OracleDriver
2jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl
3jdbc.username=C##java06
4jdbc.password=java123

    JDBCFactory.java类

1 1 package com.demo1207;
2 2 
3 3 import java.io.IOException;
4 4 import java.io.InputStream;
5 5 import java.sql.Connection;
6 6 import java.sql.DriverManager;
7 7 import java.sql.ResultSet;
8 8 import java.sql.SQLException;
9 9 import java.sql.Statement;
1010 import java.util.Properties;
1111 
1212 public class JDBCFactory {
1313  //    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
1414 //    private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
1515 //    private static final String USERNAME = "C##java06";
1616 //    private static final String PASSWORD = "java123";
1717 
1818     static String DRIVER;
1919     static String URL;
2020     static String USERNAME;
2121     static String PASSWORD;
2222     static{
2323         //只会在类第一次加载时被执行一次，适合做资源文件的读取
2424         //加载数据库配置文件资源
2525         Properties pro = new Properties();
2626         //把资源读取成字节输入流
2727         InputStream is = JDBCFactory.class.getResourceAsStream("jdbc.properties");
2828         
2929         try {
3030             //通过资源对象加载字节输入流
3131             pro.load(is);
3232             //资源对象通过key来获取对应的文件中的值，注意：静态代码块只能使用静态属性
3333             DRIVER = pro.getProperty("jdbc.driver");
3434             URL = pro.getProperty("jdbc.url");
3535             USERNAME = pro.getProperty("jdbc.username");
3636             PASSWORD = pro.getProperty("jdbc.password");
3737         } catch (IOException e) {
3838             // TODO Auto-generated catch block
3939             e.printStackTrace();
4040         }
4141     }
4242     
4343     /**
4444      * 获取数据库链接
4545      * @return 如果有异常则会返回null
4646      */
4747     public static Connection getConn(){
4848         Connection conn = null;
4949         try {
5050             Class.forName(DRIVER);
5151             conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
5252         } catch (Exception e) {
5353             e.printStackTrace();
5454         }
5555         return conn;
5656     }
5757     
5858     public static void closeAll(Connection conn,Statement st,ResultSet rs){
5959         if(conn!=null){
6060             try {
6161                 conn.close();
6262             } catch (SQLException e) {
6363                 e.printStackTrace();
6464             }
6565         }
6666         if(st!=null){
6767             try {
6868                 st.close();
6969             } catch (SQLException e) {
7070                 e.printStackTrace();
7171             }
7272         }
7373         if(rs!=null){
7474             try {
7575                 rs.close();
7676             } catch (SQLException e) {
7777                 // TODO Auto-generated catch block
7878                 e.printStackTrace();
7979             }
8080         }
8181     }
8282 }

**java实例2:**调用工厂类

在java类中写一个Student对象，用来封装学员信息
查询学员信息表，将结果集封装到List<Student>
提示：学员对象的时间字段用util.Date. 每个属性都要封装

　　Student.java

1 1 package com.demo1207;
2 2 
3 3 import java.util.Date;
4 4 
5 5 public class Student {
6 6     private int student_id;
7 7     private String student_name;
8 8     private String student_sex;
9 9     private int student_age;
1010     private int class_id;
1111     private Date birthday;
1212     public int getStudent_id() {
1313         return student_id;
1414     }
1515     public void setStudent_id(int student_id) {
1616         this.student_id = student_id;
1717     }
1818     public String getStudent_name() {
1919         return student_name;
2020     }
2121     public void setStudent_name(String student_name) {
2222         this.student_name = student_name;
2323     }
2424     public String getStudent_sex() {
2525         return student_sex;
2626     }
2727     public void setStudent_sex(String student_sex) {
2828         this.student_sex = student_sex;
2929     }
3030     public int getStudent_age() {
3131         return student_age;
3232     }
3333     public void setStudent_age(int student_age) {
3434         this.student_age = student_age;
3535     }
3636     public int getClass_id() {
3737         return class_id;
3838     }
3939     public void setClass_id(int class_id) {
4040         this.class_id = class_id;
4141     }
4242     public Date getBirthday() {
4343         return birthday;
4444     }
4545     public void setBirthday(Date birthday) {
4646         this.birthday = birthday;
4747     }
4848     
4949     
5050 }

　　JdbcTest.java

1 1 package com.demo1207;
2 2 
3 3 import java.sql.Connection;
4 4 import java.sql.DriverManager;
5 5 import java.sql.ResultSet;
6 6 import java.sql.SQLException;
7 7 import java.sql.Statement;
8 8 import java.util.ArrayList;
9 9 import java.util.List;
1010 
1111 public class JdbcTest {
1212     public static void main(String[] args) {
1313         Connection conn = null;
1414         Statement st = null;
1515         ResultSet rs = null;
1616         try {
1717             conn = JDBCFactory.getConn();
1818             System.out.println(conn);
1919             
2020             //处理sql命令的对象
2121             st = conn.createStatement();
2222             
2323             String sql = "insert into student_info values(sq_student.nextval,'叶挺',1,22,1,sysdate)";
2424             //st来执行sql语句，注意executeUpdate是执行增删改的语句
2525             st.executeUpdate(sql);
2626             
2727             //执行查询业务
2828             String sql2 = "select * from student_info";
2929             rs = st.executeQuery(sql2);
3030             List<Student> list = new ArrayList<>();
3131             while(rs.next()){
3232                 Student stu = new Student();
3333                 stu.setStudent_id(rs.getInt(1));
3434                 stu.setStudent_name(rs.getString(2));
3535                 stu.setStudent_sex(rs.getString(3));
3636                 stu.setStudent_age(rs.getInt(4));
3737                 stu.setClass_id(rs.getInt(5));
3838                 stu.setBirthday(rs.getTimestamp(6));
3939                 list.add(stu);
4040                 System.out.print(rs.getInt(1)+"\t");
4141                 System.out.print(rs.getString("student_name")+"\t");
4242                 System.out.print(rs.getString(3)+"\t");
4343                 System.out.print(rs.getInt(4)+"\t");
4444                 System.out.print(rs.getInt(5)+"\t");
4545                 System.out.println(rs.getTimestamp(6));
4646             }
4747         } catch (Exception e) {
4848             e.printStackTrace();
4949         } finally {
5050             JDBCFactory.closeAll(conn, st, rs);
5151         }
5252     }
5353 }

java实例3：调用jdbc工厂类来验证登录

1PreparedDemo.java
2
3 1 package com.demo1207;
4 2 
5 3 import java.sql.Connection;
6 4 import java.sql.PreparedStatement;
7 5 import java.sql.ResultSet;
8 6 
9 7 public class PreparedDemo {
10 8     public static void main(String[] args) {
11 9         Connection conn = null;
1210         PreparedStatement ps = null;
1311         ResultSet rs = null;
1412         
1513         try {
1614             conn = JDBCFactory.getConn();
1715             String sql = "select * from user_info where username=? and pass_word=?";
1816             ps = conn.prepareStatement(sql);
1917             //将参数占位符赋值
2018             ps.setString(1, "张三");
2119             ps.setString(2, "123456");
2220             
2321             //执行sql 和statement的执行方法一样
2422             rs = ps.executeQuery();
2523             if(rs.next()){
2624                 System.out.println(rs.getString(2)+"登录成功");
2725             }else{
2826                 System.out.println("登录失败");
2927             }
3028         } catch (Exception e) {
3129             e.printStackTrace();
3230         } finally {
3331             JDBCFactory.closeAll(conn, ps, rs);
3432         }
3533     }
3634 }

java实例4：调用工厂类进行增删改查

1 1 package com.demo1207;
2 2 
3 3 import java.sql.Connection;
4 4 import java.sql.PreparedStatement;
5 5 import java.sql.ResultSet;
6 6 import java.sql.Timestamp;
7 7 import java.util.Date;
8 8 
9 9 public class CRUDDemo {
1010     Connection conn;
1111     PreparedStatement ps;
1212     ResultSet rs;
1313     
1414     public  void create(){
1515         try {
1616             conn = JDBCFactory.getConn();
1717             
1818             String sql = "insert into student_info values(sq_student.nextval,?,?,?,?,?)";
1919             ps = conn.prepareStatement(sql);
2020             
2121             ps.setString(1, "田甜");
2222             ps.setString(2, "2");
2323             ps.setInt(3, 22);
2424             ps.setInt(4, 1);
2525             ps.setTimestamp(5, new Timestamp(new Date().getTime()));
2626             ps.executeUpdate();
2727             System.out.println("新增成功");
2828         } catch (Exception e) {
2929             e.printStackTrace();
3030         } finally {
3131             JDBCFactory.closeAll(conn, ps, rs);
3232         }
3333     }
3434     
3535     public void delete(){
3636         try {
3737             conn = JDBCFactory.getConn();
3838             
3939             String sql = "delete from student_info where student_id=?";
4040             ps = conn.prepareStatement(sql);
4141             
4242             ps.setInt(1, 1);
4343             ps.executeUpdate();
4444             System.out.println("删除成功");
4545         } catch (Exception e) {
4646             e.printStackTrace();
4747         } finally {
4848             JDBCFactory.closeAll(conn, ps, rs);
4949         }
5050     }
5151     
5252     public void update(){
5353         try {
5454             conn = JDBCFactory.getConn();
5555             
5656             String sql = "update student_info set student_name=?,student_sex=?,student_age=?,class_id=?,birthday=? where student_id=?";
5757             ps = conn.prepareStatement(sql);
5858             
5959             ps.setString(1, "哈哈");
6060             ps.setString(2, "2");
6161             ps.setInt(3, 28);
6262             ps.setInt(4, 1);
6363             ps.setTimestamp(5, new Timestamp(new Date().getTime()));
6464             ps.setInt(6, 2);
6565             ps.executeUpdate();
6666             System.out.println("修改成功");
6767         } catch (Exception e) {
6868             e.printStackTrace();
6969         } finally {
7070             JDBCFactory.closeAll(conn, ps, rs);
7171         }
7272     }
7373     
7474     public void query(){
7575         try {
7676             conn = JDBCFactory.getConn();
7777             
7878             String sql = "select * from student_info";
7979             ps = conn.prepareStatement(sql);
8080             
8181             rs = ps.executeQuery();
8282             while(rs.next()){
8383                 System.out.println();
8484             }
8585         } catch (Exception e) {
8686             e.printStackTrace();
8787         } finally {
8888             JDBCFactory.closeAll(conn, ps, rs);
8989         }
9090     }
9191 }

作业：

1一。用户管理
21.用户注册：要求用户名不能重复
32.用户登录
4二。联系人组群管理
51.用户新建联系人组群：每个用户注册时都会默认新建一个名字叫“我的联系人”这么一个组群，该组群不能修改，
6                每个用户可以新建n个联系人组群。
72.用户修改联系组群：修改组群名字
83.用户删除组群：删除组群后，将该组的所有联系人移到默认组群“我的联系人”
9三。联系人管理
101.用户新建联系人：需要指定联系人到哪个组群。联系人信息（姓名，年龄，性别，移动电话，固定电话，生日，邮箱）
112.用户修改联系人：可以修改联系人所有信息，包括组群
123.用户删除联系人：
134.查询联系人：
14    a.按姓名模糊查询
15    b.按组群查询
16    c.按电话号码模糊查询