JDBC 基础应用

package com.rain.DB;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import com.rain.user.UserEO;public class DB {ArrayList<UserEO> userList = null;UserEO user = null;Connection conn = null;ResultSet rs = null;PreparedStatement ps = null;public ArrayList<UserEO> ReadUser() {userList = new ArrayList<UserEO>();String sql = "select * from student";try {conn = DBUtil.getConnection();ps = conn.prepareStatement(sql);rs = ps.executeQuery();while (rs.next()) {user = new UserEO();user.setUserName(rs.getString("name"));user.setPassword(rs.getString("password"));userList.add(user);System.out.println(userList.size() + "数据库里有几个学生");}} catch (SQLException e) {e.printStackTrace();System.out.println("function ReadUser error");} finally {DBUtil.close(conn, ps, rs);}return userList;}public UserEO getUserById(int id) {String sql = "select * from student where id=?";try {conn = DBUtil.getConnection();ps = conn.prepareStatement(sql);ps.setInt(1, id);rs = ps.executeQuery();if (!rs.next()) {return null;} else {user = new UserEO();user.setUserName(rs.getString("name"));user.setPassword(rs.getString("password"));}} catch (Exception e) {e.printStackTrace();System.out.println("function getUserById error");} finally {DBUtil.close(conn, ps, rs);}return user;}public int addUser(UserEO user) {int i = 1;String sql = "insert into student (" + "name," + "password" + ")"+ "values(?,?)";try {conn = DBUtil.getConnection();ps = conn.prepareStatement(sql);ps.setString(i++, user.getUserName());ps.setString(i++, user.getPassword());i = ps.executeUpdate();} catch (Exception e) {e.printStackTrace();System.out.println("function addProduct error");} finally {DBUtil.close(conn, ps, rs);}// System.out.println(id);return i;}public int deleteUser(int id) {int i = 0;String sql = "delete from student where id=?";try {conn = DBUtil.getConnection();ps = conn.prepareStatement(sql);ps.setInt(1, id);i = ps.executeUpdate();} catch (Exception e) {e.printStackTrace();System.out.println("function deleteUser error");} finally {DBUtil.close(conn, ps, rs);}return i;}public int updateUser(UserEO user) {String sql = "  update student set name=?,password=? where id=?;" ;int i = 1;try {conn = DBUtil.getConnection();ps = conn.prepareStatement(sql);ps.setString(i++, user.getUserName());ps.setString(i++, user.getPassword());ps.setInt(i++, Integer.parseInt(user.getId()));i = ps.executeUpdate();} catch (Exception e) {e.printStackTrace();System.out.println("function updateUser error");} finally {DBUtil.close(conn, ps, rs);}return i;}public static void main(String[] args) {// add//DB db = new DB();//UserEO u=new UserEO();//u.setUserName("rain");//u.setPassword("rain");//db.addUser(u);// update//DB db = new DB();//UserEO u=new UserEO();//u.setUserName("x");//u.setPassword("xx");//u.setId("2");//db.updateUser(u);// delete//DB db = new DB();//db.deleteUser(1);// read//DB db = new DB();//UserEO u=new UserEO();//u=db.getUserById(2);////if (u == null)//System.out.println("do no hava this user");//else//System.out.println(u.getUserName());}}