通过JDBC往Oracle中插入timestamp类型数据

通过JDBC往Oracle中插入timestamp类型数据

表结构：

create table TEST(
  ID       INTEGER,
  BIRTHDAY TIMESTAMP
);

 

 使用JDBC将日期插入到TIMESTAMP类型字段：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
 
public class Test {
     
    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
 
    /**
     * 获得Connection
     * 
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl", "root", "root");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
 
    public static void main(String[] args) throws SQLException {
        // TODO Auto-generated method stub
        PreparedStatement pst = null;
        Connection conn = getConnection();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String myDate = df.format(new Date()); //当前时间
        String sql = "insert into test(id,birthday) values('1',to_timestamp('" + myDate + "','yyyy-mm-dd hh24:mi:ss'))";
        pst = conn.prepareStatement(sql);
         
        pst.executeUpdate();
        // 省略IO流close
    }
 
}

 如上例子中是使用to_timestamp将字符型转成timestamp来达到插入timestamp数据的目的。但是当前的insert语句只使用于oracle，当环境变更为mysql时则不行（没有to_timestamp函数）。

为此我们做出如下修改：

package com.ssh.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Date;
 
public class Test {
     
    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
 
    /**
     * 获得Connection
     * 
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/orcl", "root", "root");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
 
    public static void main(String[] args) throws SQLException {
        // TODO Auto-generated method stub
        PreparedStatement pst = null;
        Connection conn = getConnection();
        String sql = "insert into test(id,birthday) values('1'," + new Timestamp(System.currentTimeMillis()) + ")";
        pst = conn.prepareStatement(sql);
         
        pst.executeUpdate();
        // 省略IO流close
    }
 
}

 这样做到了兼容。