首先先创建数据库表:(这里分为两个字段,字段分别是userName和regDate,用于观察注册人还有时间)
create table emp(
userName varchar2(20),
regDate Date
);
第二步:
创建javaBean类,这里Bean名可以随便起。(其实javaBean就是一个类,面像对像过程的优点),
代码如下:
package entity;
public class Emp {
private String userName;
private String regDate;
//Setter和Getter方法
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getRegDate() {
return regDate;
}
public void setRegDate(String regDate) {
this.regDate = regDate;
}
}
第三步,创建数据库操作基类:
package dao.impl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class BaseDao {
protected Connection connect = null;// 可用连接
// 驱动类
private static final String driver = "oracle.jdbc.driver.OracleDriver";
// 连接服务器字符串
private static final String url = "jdbc:oracle:thin:@localhost:1521:aq";
// 用户名
private static final String username = "ynr";
// 密码
private static final String password = "123456";
protected PreparedStatement pstmt = null;
protected ResultSet rs = null;
/**
* 获取可用连接
*
* @return
* @throws ClassNotFoundException
* @throws
* @throws Exception
*/
public boolean getConnection() {
try {
Class.forName(driver);
connect = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
return false;
} catch (SQLException e) {
e.printStackTrace();
return false;
}
return true;
}
/**
* 关闭可用连接
*
* @throws Exception
*/
public boolean closeAll() {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// 关闭Statement
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// 关闭连接
if (connect != null) {
try {
connect.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
}
}
return true;
}
/**
* 增,删,改
*/
public int Update(String sql, Object[] params) {
int result = 0;
//清空数据,防止SQL注入式攻击
pstmt = null;
connect = null;
rs= null;
try {
getConnection();
pstmt = connect.prepareStatement(sql);
// 填充的参数方式1 // int index=1;
// if (params !=null) {
// for (Object object : params) {
// pstmt.setObject(index++, object);
// }
// }
// 填充的参数方式2
if (params != null) {
for (int i = 0; i < params.length; i++) {
pstmt.setObject(i + 1, params[i]);
}
}
result = pstmt.executeUpdate();
} catch (SQLException e) {
System.out.println("executeUpdate" + e.getMessage());
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
closeAll();
} catch (Exception e) {
System.out.println("执行增,删,改关闭连接时产生异常" + e.getMessage());
e.printStackTrace();
}
}
return result;
}
/**
* 查询
*/
public ResultSet Query(String sql, Object[] params) {
//清空数据,防止SQL注入式攻击
pstmt = null;
connect = null;
rs= null;
try {
getConnection();
pstmt = connect.prepareStatement(sql);
int index = 1;
for (Object object : params) {
pstmt.setObject(index++, object);
}
rs = pstmt.executeQuery();
} catch (SQLException e) {
System.out.println("executeQuery" + e.getMessage());
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
// finally {
// try {
// closeAll();
// } catch (Exception e) {
// System.out.println("执行查询关闭连接时产生异常" + e.getMessage());
// e.printStackTrace();
// }
// }
return rs;
}
}
第四步:
为了减少开发的藕合,也就是减少和与合作的的人员的命名冲突,这里用接口来,接口一般在其名称加上一个" I “(interface的首字母I):
代码如下:
package dao;
import java.util.List;
import entity.Emp;
public interface RegDao {
boolean add(Emp date); //插入信息
List<Emp> getEmpsList(); //查询信息
}
第五步:
编写实现类(两种方式:)
第一种:
package dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import dao.RegDao;
import entity.Emp;
public class RegDaoImpl extends BaseDao implements RegDao {
@Override
public boolean add(Emp date) {
String sql = "insert into emp(userName,regDate)values(?,to_date(?,'yyyy-mm-dd hh24:mi:ss'))";
int i = 0;
Object[] params = {date.getUserName(),date.getRegDate()};
i = this.Update(sql, params);
return false;
}
@Override
public List<Emp> getEmpsList() {
String sql = "select UserName,RegDate from emp";
Object[] params ={};
List<Emp> empList = new ArrayList<Emp>();
ResultSet rs = this.Query(sql, params);
try {
while (rs.next()) {
Emp emp = new Emp();
emp.setUserName(rs.getString("UserName"));
emp.setRegDate(rs.getString("RegDate"));
; empList.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
return empList;
}
public static void main(String[] args) {
RegDaoImpl daoImpl = new RegDaoImpl();
Date date = new Date();
Emp emp = new Emp();
emp.setUserName("www");
emp.setRegDate(date.toLocaleString());
daoImpl.add(emp);
List<Emp> List = daoImpl.getEmpsList();
for (Emp emp2 : List) {
System.out.println(emp2.getUserName()+"\t" + emp2.getRegDate());
}
}
}
可以发现,这种方法是上一篇文章说的有精确时间的方式。
第二种方法:
package dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import dao.RegDao;
import entity.Emp;
public class RegDaoImpl extends BaseDao implements RegDao {
@Override
public boolean add(Emp date) {
String sql = "insert into emp(userName,regDate)values(?,to_date(?,'yyyy-mm-dd '))";//注意看一下这里①
int i = 0;
Object[] params = {date.getUserName(),date.getRegDate()};
i = this.Update(sql, params);
return false;
}
@Override
public List<Emp> getEmpsList() {
String sql = "select UserName,RegDate from emp";
Object[] params ={};
List<Emp> empList = new ArrayList<Emp>();
ResultSet rs = this.Query(sql, params);
try {
while (rs.next()) {
Emp emp = new Emp();
emp.setUserName(rs.getString("UserName"));
emp.setRegDate(rs.getString("RegDate"));
; empList.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
return empList;
}
public static void main(String[] args) {
RegDaoImpl daoImpl = new RegDaoImpl();
Date date = new Date();
Emp emp = new Emp();
emp.setUserName("www");
SimpleDateFormat fomat = new SimpleDateFormat("yyyy-MM-dd"); //注意看这里②
emp.setRegDate(fomat.format(date));
daoImpl.add(emp);
List<Emp> List = daoImpl.getEmpsList();
for (Emp emp2 : List) {
System.out.println(emp2.getUserName()+"\t" + emp2.getRegDate());
}
}
}
注:①和②的小细节,这里它是不带时分秒的,
带时分秒的代码如下:
package dao.impl;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import dao.RegDao;
import entity.Emp;
public class RegDaoImpl extends BaseDao implements RegDao {
@Override
public boolean add(Emp date) {
String sql = "insert into emp(userName,regDate)values(?,to_date(?,'yyyy-mm-dd hh-mm-ss'))";//①注意!!!
int i = 0;
Object[] params = {date.getUserName(),date.getRegDate()};
i = this.Update(sql, params);
return false;
}
@Override
public List<Emp> getEmpsList() {
String sql = "select UserName,RegDate from emp";
Object[] params ={};
List<Emp> empList = new ArrayList<Emp>();
ResultSet rs = this.Query(sql, params);
try {
while (rs.next()) {
Emp emp = new Emp();
emp.setUserName(rs.getString("UserName"));
emp.setRegDate(rs.getString("RegDate"));
; empList.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}
return empList;
}
public static void main(String[] args) {
RegDaoImpl daoImpl = new RegDaoImpl();
Date date = new Date();
Emp emp = new Emp();
emp.setUserName("www");
SimpleDateFormat fomat = new SimpleDateFormat("yyyy-MM-dd hh-mm-ss"); <span style="font-family: Arial, Helvetica, sans-serif;">//②注意!!!</span>
emp.setRegDate(fomat.format(date));
daoImpl.add(emp);
List<Emp> List = daoImpl.getEmpsList();
for (Emp emp2 : List) {
System.out.println(emp2.getUserName()+"\t" + emp2.getRegDate());
}
}
}
带时分秒的时候它提示这样的一个错误:
java.sql.SQLException: ORA-01810: 格式代码出现两次,其中②hh-mm-ss这个不要,或者①这里不要,它都会出现相应的错误哦
第二种方法执行之后,得到的数据都是时分秒都为0的