MyEclipse实现库表数据增删改查

----创建数据库表t_user

create table t_user
(
  user_id  INTEGER primary key,
  username VARCHAR2(50),
  password VARCHAR2(50) not null,
  status   INTEGER,
  role     NUMBER(3)
);

select * from t_user;

----创建JDBCUtilProperties封装代码块

public class JDBCUtilProperties {

//静态代码块
static public Properties pt = null;
static public FileInputStream  fs = null;

static {
//创建输入流对象，用于去配置文件中读取数据
pt = new Properties();
try {
fs = new FileInputStream("src/OracleJDBC.properties");
} catch (FileNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}

//load():可以将流对象中的数据值以键值对的形式加载到Properties集合中
try {
pt.load(fs);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


static public Connection getConnection()throws Exception{

Class.forName(pt.getProperty("driver"));
Connection conn=DriverManager.getConnection(pt.getProperty("url"),pt.getProperty("username"),pt.getProperty("password"));
return conn;
}

static public void close(Connection conn,PreparedStatement ps){

if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

//重写
static public void close(Connection conn,PreparedStatement ps,ResultSet rs){

if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(ps!=null){
try {
ps.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

}
}

----创建一个user类，和UserDaoInterface接口

public interface UserDaoInterface {


public void insertUser(User user);
public void deleteUser(String name);
public void exchangeUser(int user_id);
public List selectUser();
}

----创建UserDao类去实现接口

//添加数据
public void insertUser(User user){

Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtilProperties.getConnection();
String sqlStr =  "insert into t_user values(?,?,?,?,?)";
ps = conn.prepareStatement(sqlStr);
ps.setInt(1, user.getUser_id());
ps.setString(2, user.getUsername());
ps.setString(3, user.getPassword());
ps.setInt(4, user.getUser_status());
ps.setInt(5, user.getUser_role());
 
ps.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtilProperties.close(conn, ps);
}
}

//删除数据
public void deleteUser(String username){
Connection conn = null;
PreparedStatement ps = null;

try {
conn = JDBCUtilProperties.getConnection();
String sqlStr = "delete from t_user where username = ?";
ps = conn.prepareStatement(sqlStr);
ps.setString(1,username);
int a = ps.executeUpdate();
if(a>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtilProperties.close(conn, ps);
}
}

//修改数据
public void exchangeUser(int user_id){

User user= new User();
user.setUsername("Jerry");
user.setPassword("1234");
user.setUser_role(1);
user.setUser_status(0);

Connection conn = null;
PreparedStatement ps = null;

try {
conn = JDBCUtilProperties.getConnection();
String sqlStr = "update t_user set username=?,password=?,status=?,role=? where user_id=?";
ps = conn.prepareStatement(sqlStr);

ps.setString(1,user.getUsername());
ps.setString(2, user.getPassword());
ps.setInt(3, user.getUser_status());
ps.setInt(4, user.getUser_role());
ps.setInt(5, user_id);

   ps.executeUpdate();

} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtilProperties.close(conn, ps);
}

   }


//查询数据
public List selectUser(){
List<User> list = new ArrayList<User>();

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
conn = JDBCUtilProperties.getConnection();
String sqlStr = "select * from t_user";
ps = conn.prepareStatement(sqlStr);
rs = ps.executeQuery();
while(rs.next()){
User user = new User();

user.setUser_id(rs.getInt(1));
user.setUsername(rs.getString(2));
user.setPassword(rs.getString(3));
user.setUser_status(rs.getInt(4));
user.setUser_role(rs.getInt(5));

list.add(user);

}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
}

----创建测试类

public class DaoTest {


@Test
public void test_1(){

User user= new User();
user.setUser_id(7);
user.setUsername("Jerry");
user.setPassword("1234");
user.setUser_role(1);
user.setUser_status(0);

UserDao userDao = new UserDao();
//userDao.insertUser(user);//增加数据
//userDao.deleteUser("Lisa");//删除数据
//userDao.selectUser();//查询数据
userDao.exchangeUser(10);//修改数据
/*List list = userDao.selectUser();//查询数据并将数据遍历

for(Object obj:list){
User u = (User)obj;
System.out.println(user.getUser_id()+","+user.getUsername()+","+user.getPassword()+","+user.getUser_status()+","+user.getUser_role());
}*/
}
}