千锋学习的43天:JDBC
今天学习的是ORA,DAO,日期转换
ORa表映射成类,列映射成属性
在这里插入代码片
import java.util.Date;
public class UserInfo {
// user_id INT PRIMARY KEY,
// user_name VARCHAR(10)NOT NULL,
// user_pwd VARCHAR(10) NOT NULL,
// user_borndate DATE,
// user_email VARCHAR(10),
// user_address VARCHAR(10)
private int user_id;
private String user_name;
private String user_pwd;
private Date user_borndate;
private String user_email;
private String user_address;
public UserInfo(int user_id, String 你ha, String user_pwd, String s, String user_email, String user_address) { }
public UserInfo(int user_id, String user_name, String user_pwd, Date user_borndate, String user_email, String user_address) {
this.user_id = user_id;
this.user_name = user_name;
this.user_pwd = user_pwd;
this.user_borndate = user_borndate;
this.user_email = user_email;
this.user_address = user_address;
}
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public String getUser_pwd() {
return user_pwd;
}
public void setUser_pwd(String user_pwd) {
this.user_pwd = user_pwd;
}
public Date getUser_borndate() {
return user_borndate;
}
public void setUser_borndate(Date user_borndate) {
this.user_borndate = user_borndate;
}
public String getUser_email() {
return user_email;
}
public void setUser_email(String user_email) {
this.user_email = user_email;
}
public String getUser_address() {
return user_address;
}
public void setUser_address(String user_address) {
this.user_address = user_address;
}
@Override
public String toString() {
return "UserInfo{" +
"user_id=" + user_id +
", user_name='" + user_name + '\'' +
", user_pwd='" + user_pwd + '\'' +
", user_borndate=" + user_borndate +
", user_email='" + user_email + '\'' +
", user_address='" + user_address + '\'' +
'}';
}
}
日期转换,字符串可以转换成Utildate数据,不可以转换成Sqldate,Sqldate是util的子类,需要utildate转换成sqldate数据。
public class DateUtil {
private static final SimpleDateFormat simp=new SimpleDateFormat("yyyy-MM-dd");
public static java.util.Date toUtilDate(String str){
try {
return simp.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
return null;
}
public static java.sql.Date toSqlDate(java.util.Date date){
return new java.sql.Date(date.getTime());
}
}
DAO数据库操作语句
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
// user_id INT PRIMARY KEY,
// user_name VARCHAR(10)NOT NULL,
// user_pwd VARCHAR(10) NOT NULL,
// user_borndate DATE,
// user_email VARCHAR(10),
// user_address VARCHAR(10)
public class UserInfoDao {
private Connection connection=null;
private PreparedStatement preparedStatement=null;
private ResultSet resultSet=null;
public int insert(UserInfo userInfo){
connection = AccessDb.get();
String sql="insert into userinfo (user_id,user_name,user_pwd,user_borndate,user_email,user_address)values(?,?,?,?,?,?);";
try {
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setInt(1,userInfo.getUser_id());
preparedStatement.setString(2,userInfo.getUser_name());
preparedStatement.setString(3,userInfo.getUser_pwd());
preparedStatement.setDate(4,DateUtil.toSqlDate(userInfo.getUser_borndate()));
preparedStatement.setString(5,userInfo.getUser_email());
preparedStatement.setString(6,userInfo.getUser_address());
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return 0;
}
public int delete(int id){
connection = AccessDb.get();
String sql="delete from userinfo where user_id=?;";
try {
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
AccessDb.closeAll(connection,preparedStatement,null);
}
return 0;
}
public int update(UserInfo userinfo){
connection = AccessDb.get();
String sql="update userinfo set user_name=?,user_pwd=?,user_borndate=?,user_email=?,user_address=? where user_id=?;";
try {
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setString(1,userinfo.getUser_name());
preparedStatement.setString(2,userinfo.getUser_pwd());
preparedStatement.setDate(3,DateUtil.toSqlDate(userinfo.getUser_borndate()));
preparedStatement.setString(4,userinfo.getUser_email());
preparedStatement.setString(5,userinfo.getUser_address());
preparedStatement.setInt(6,userinfo.getUser_id());
return preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
AccessDb.closeAll(connection,preparedStatement,null);
}
return 0;
}
public UserInfo select(int id){
connection = AccessDb.get();
String sql="Select user_id,user_name,user_pwd,user_borndate,user_email,user_address from userinfo where user_id=?;";
try {
preparedStatement=connection.prepareStatement(sql);
preparedStatement.setInt(1,id);
resultSet=preparedStatement.executeQuery();
if(resultSet.next()){
int i= resultSet.getInt("user_id");
String username=resultSet.getString("user_name");
String password=resultSet.getString("user_pwd");
Date date=resultSet.getDate("user_borndate");
String email=resultSet.getString("user_email");
String address= resultSet.getString("user_address");
UserInfo user=new UserInfo(i,username,password,date,email,address);
return user;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
AccessDb.closeAll(connection,preparedStatement,resultSet);
}
return null;
}
public List<UserInfo> selectall(){
connection = AccessDb.get();
String sql="Select user_id,user_name,user_pwd,user_borndate,user_email,user_address from userinfo;";
try {
preparedStatement=connection.prepareStatement(sql);
resultSet=preparedStatement.executeQuery();
List<UserInfo> ul=new ArrayList<UserInfo>();
while(resultSet.next()) {
int i = resultSet.getInt("user_id");
String username = resultSet.getString("user_name");
String password = resultSet.getString("user_pwd");
Date date = resultSet.getDate("user_borndate");
String email = resultSet.getString("user_email");
String address = resultSet.getString("user_address");
UserInfo user = new UserInfo(i, username, password, date, email, address);
ul.add(user);
}
return ul;
}catch(Exception e){
e.printStackTrace();
}finally {
AccessDb.closeAll(connection,preparedStatement,resultSet);
}
return null;
}
}
在这里插入代码片
数据库连接
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class AccessDb {
private static final Properties properties = new Properties();
static{
InputStream input=AccessDb.class.getResourceAsStream("db.properties");
try {
properties.load(input);
Class.forName(properties.getProperty("driver"));
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection get(){
try {
Connection connection= DriverManager.getConnection(properties.getProperty("url"),properties.getProperty("user"),properties.getProperty("password"));
return connection;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void closeAll(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
try {
if(connection!=null){
connection.close();
}
if(preparedStatement!=null){
preparedStatement.close();
}
if (resultSet!=null){
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}