package com.czz.daosimpl;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* derbyDAO
*
* @author Marshal Wu
*
*/
public class DerbyUserDao implements ManagerUserDao {
private Connection getConnection() throws SQLException {
Connection connection = DriverManager
.getConnection("jdbc:derby://localhost/test;user=test;password=test");
connection.setAutoCommit(false);
return connection;
}
public void delete(User user) {
Connection connection = null;
try {
connection = this.getConnection();
PreparedStatement statement = connection
.prepareStatement("delete from user_info where id=?");
statement.setLong(1, user.getId());
statement.execute();
statement.close();
connection.commit();
} catch (SQLException e) {
this.rollback(connection);
throw new RegisterException(e);
} finally {
this.close(connection);
}
}
public User get(long id) {
Connection connection = null;
try {
connection = this.getConnection();
PreparedStatement statement = connection
.prepareStatement("select id,user_name,email,birthday from user_info where id=?");
statement.setLong(1, id);
ResultSet resultSet = statement.executeQuery();
User user = null;
if (resultSet.next()) {
user = getUser(resultSet);
}
statement.close();
connection.commit();
return user;
} catch (SQLException e) {
this.rollback(connection);
throw new RegisterException(e);
} finally {
this.close(connection);
}
}
private User getUser(ResultSet resultSet) throws SQLException {
User user;
user = new User();
user.setId(resultSet.getLong("id"));
user.setName(resultSet.getString("user_name"));
user.setEmail(resultSet.getString("email"));
user.setBirthday(resultSet.getDate("birthday"));
return user;
}
public List<User> getAll() {
Connection connection = null;
try {
connection = this.getConnection();
PreparedStatement statement = connection
.prepareStatement("select id,user_name,email,birthday from user_info");
ResultSet resultSet = statement.executeQuery();
List<User> list = new ArrayList<User>();
while (resultSet.next()) {
User user = getUser(resultSet);
list.add(user);
}
statement.close();
connection.commit();
return list;
} catch (SQLException e) {
this.rollback(connection);
throw new RegisterException(e);
} finally {
this.close(connection);
}
}
public void save(User user) {
if (user.getId() == 0) {
this.create(user);
} else {
this.update(user);
}
}
private void update(User user) {
Connection connection = null;
try {
connection = this.getConnection();
PreparedStatement statement = connection
.prepareStatement("update user_info set user_name=?,email=?,birthday=? where id=?");
int index = 1;
statement.setString(index++, user.getName());
statement.setString(index++, user.getEmail());
statement.setDate(index++, new Date(user.getBirthday().getTime()));
statement.setLong(index++, user.getId());
statement.execute();
statement.close();
connection.commit();
} catch (SQLException e) {
this.rollback(connection);
throw new RegisterException(e);
} finally {
this.close(connection);
}
}
private void create(User user) {
Connection connection = null;
try {
connection = this.getConnection();
PreparedStatement statement = connection
.prepareStatement("insert into user_info(user_name,email,birthday) values(?,?,?)");
int index = 1;
statement.setString(index++, user.getName());
statement.setString(index++, user.getEmail());
if (user.getBirthday() == null) {
statement.setDate(index++, null);
} else {
statement.setDate(index++, new Date(user.getBirthday()
.getTime()));
}
statement.execute();
statement.close();
user.setId(this.getId(connection));
connection.commit();
} catch (SQLException e) {
this.rollback(connection);
throw new RegisterException(e);
} finally {
this.close(connection);
}
}
private int getId(Connection connection) throws SQLException {
CallableStatement callableStatement = connection
.prepareCall("values identity_val_local()");
ResultSet resultSet = callableStatement.executeQuery();
resultSet.next();
int id = resultSet.getInt(1);
resultSet.close();
callableStatement.close();
return id;
}
private void rollback(Connection connection) {
if (connection != null) {
try {
connection.rollback();
} catch (SQLException e) {
}
}
}
private void close(Connection connection) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
}
}
}
public void deleteAll() {
}
}
java代码
最新推荐文章于 2022-01-19 16:31:02 发布