JDBC:从入门到放弃（二）

ConnectionFactory

在使用ConnectionFactory之前，请保证电脑上已经安装了oracle数据库，Navicat，以及授权了新注册的用户。同时，下载oracle对应的jdbc驱动包，这里采用的是ojdbc6的jar包，具体下载链接可以点这个，有关oracle以及相关用户授权的操作，可以参见这篇博文，写的很清楚了。

示例1

使用ConnectionFactory简单工厂，创建Oracle的Connection对象

目录结构如下

1.png

jdbcinfor.properties

oracle.driver=oracle.jdbc.driver.OracleDriver
oracle.url=jdbc:oracle:thin:@localhost:1521:XE
oracle.user=guest
oracle.password=guest

工具类：ConnectionFactory.class

package com.iotek.common;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class ConnectionFactory {
    private static String DRIVER;
    private static String URL;
    private static String USER;
    private static String PASSWORD;

    static {
        Properties properties = new Properties();
        InputStream iStream = ConnectionFactory.class.getResourceAsStream("jdbcinfo.properties");
        try {
            properties.load(iStream);
            DRIVER = properties.getProperty("oracle.driver");
            URL = properties.getProperty("oracle.url");
            USER = properties.getProperty("oracle.user");
            PASSWORD = properties.getProperty("oracle.password");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return connection;
    }
    
    public static void main(String[] args) {
        System.out.println(ConnectionFactory.getConnection());
    }
}

这里记得要导入相应的ojdbc6.jar的包，否则会报ClassNotFound的异常。

示例2

创建student表

create table student(
  id number(10) primary key,
  name varchar2(20) not null,
  address varchar2(30),
  gender varchar2(10),
  age number(3) chec(age between 1 and 100)
);

创建序列

create sequence student_seq
  start with 1
  increment by 1;

创建student表对应的student类，object-relation-map,简称：对象关系映射

package com.iotek.pojo;

import java.io.Serializable;

public class Student implements Serializable {

    private Long id;
    private String name;
    private String address;
    private String gender;
    private Integer age;

    public Student() {
    }

    public Student(Long id, String name, String address, String gender, Integer age) {
        super();
        this.id = id;
        this.name = name;
        this.address = address;
        this.gender = gender;
        this.age = age;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", address=" + address + ", gender=" + gender + ", age=" + age
                + "]";
    }

}

创建studentJDbc类

package com.iotek.advance;

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

import com.iotek.common.ConnectionFactory;
import com.iotek.pojo.Student;

public class StudentJdbc {
    public void save(Student student) {
        
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        
        try {
            
            connection = ConnectionFactory.getConnection();
            String selectSQL = "SELECT student_seq.nextval FROM dual";
            preparedStatement = connection.prepareStatement(selectSQL);
            resultSet = preparedStatement.executeQuery();
            Long id = null;
            
            if (resultSet.next()) {
                id = resultSet.getLong(1);
            }
            
            String insertSQL = "INSERT INTO student VALUES(?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(insertSQL);
            preparedStatement.setLong(1, id);
            preparedStatement.setString(2, student.getName());
            preparedStatement.setString(3, student.getAddress());
            preparedStatement.setString(4, student.getGender());
            preparedStatement.setInt(5, student.getAge());
            
            int rows = preparedStatement.executeUpdate();
            System.out.println("受影响的行数：" + rows);
            
        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            
        }       
    }
}

JDBC操作数据库之后，一定要记得将connection，statement，以及结果集resultSet进行关闭。这里写一个工具类，进行此类操作

负责关闭释放内存的工具类UtilsJdbc

class UtilsJdbc {
  public static void close(Connection conn, Statement stmp, ResultSet rs) {
    if ( conn != null) {
      try {
        conn.close();
      } catch(Exception e) {
         e.printStackTrace();
      }
    } 
  }
}
其他的statement以及resultset的释放与上面同理，当然也可以重载该方法。