JDBC(4)

来源：互联网发布：编程的本质 pdf 微盘编辑：程序博客网时间：2024/06/03 23:40

package day03;import java.sql.Connection;import java.sql.Statement;import day20151100.DBUtil2;public class BatchDemo {    public static void main(String[] args) {        try{            Connection conn = DBUtil2.getConnection();            Statement state = conn.createStatement();            for(int i=1000;i<2000;i++){                String sql = "insert into user_ls(id) "                        + "values("+i+")";                //缓存，等待一起执行                state.addBatch(sql);                //为了不吃内存，缓存500条执行一次批处理                if(i%500==0){                    state.executeBatch();                    //然后清空一下，以便缓存新的                    state.clearBatch();                }            }            //执行批处理            state.executeBatch();            //state.clearBatch();        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }    }}

package day03;import java.sql.Connection;import java.sql.PreparedStatement;import day20151100.DBUtil2;public class BatchDemo2 {    public static void main(String[] args) {        try{            Connection conn = DBUtil2.getConnection();            String sql = "insert into user_ls(id,name) "                    + "values(?,?)";            PreparedStatement ps = conn.prepareStatement(sql);            for(int i=9500;i<10500;i++){                ps.setInt(1, i);                ps.setString(2, "test"+i);                ps.addBatch();            }            ps.executeBatch();        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }    }}

package day03;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import day20151100.DBUtil2;/** * 向dept表中追加一个部门 * 同时该部门添加一个用户 * */public class JDBCDemo1 {    /*     * 1:先插入一个新的部门记录     * 2：再向user表中插入一个员工信息     *   但同时该员工的部门编号应该是     *   新插入的部门记录的主键值     */    public static void main(String[] args) {        try{            Connection conn = DBUtil2.getConnection();            Statement state = conn.createStatement();            /*             * 先插入一个部门             */            String sql = "insert into dept_ls values(dept_seq_ls.nextval,'java','shanghai')";            state.executeUpdate(sql);            /*             * 获取刚刚插入的部门记录的主键值，             * 用于作为user表中新记录外键的值             */            String idSql = "select max(deptno) from dept_ls";            ResultSet rs = state.executeQuery(idSql);            int id = 0;//新插入部门id            if(rs.next()){                id = rs.getInt(1);                //如果不写1，上面的MAX(deptno)需要加别名            }            rs.close();//释放资源            /*             * 添加新user             */            String userSql = "insert into user_ls(id,name,deptno) "                    + "values(user_seq_ls.nextval,'jackson',"+id+")";            state.executeUpdate(userSql);            System.out.println("保存完毕");            /*             * 当statement使用完毕，也应当关闭             */            state.close();        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }    }}

package day03;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import day20151100.DBUtil2;/** * preparedStatement支持一个方法 * 可以在执行插入操作后，获取该条语句 * 在数据库中产生的记录中每个字段的值 * 有个这个功能，我们在向从表中掺入数据时 * 可以获取该主键作为外键插入，而无需因为 * 要获取主表中的值而进行一次查询 * */public class JDBCDemo2 {    public static void main(String[] args){        try{            Connection conn = DBUtil2.getConnection();            String deptSql="insert into dept_ls "                    + "values(dept_seq_ls.nextval,?,?)";            System.out.println(deptSql);            /*             * 创建PreparedStatement时，可以             * 使用Connection的重载方法，第二个参数             * 要求我们传入一个字符串数组，用来             * 指定当通过ps执行插入操作后，该记录在             * 表中想获取的值所在的字段名。             */            PreparedStatement ps = conn.prepareStatement(                    deptSql,new String[]{"deptno","dname"});            ps.setString(1, "oracle");            ps.setString(2, "naijing");            if(ps.executeUpdate()>0){                /*                 * 获取刚刚插入进去的记录中                 * 关注的那几列的值                 */                ResultSet rs = ps.getGeneratedKeys();                if(rs.next()){                    int deptno = rs.getInt(1);                    System.out.println("该部门编号："+deptno);                    //rs.getString(2);                }                rs.close();            }            ps.close();        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }    }}

package day03;import java.util.List;/** * 业务逻辑层不再关心数据怎么来 * @author Administrator * */public class MyUserService {    public static void main(String[] args) {        UserDAO dao = new UserDAO();        if(dao.deleteById(1003)){            System.out.println("删除成功");        }else{            System.out.println("删除失败");        }//      User user = new User();//      user.setName("martin");//      user.setPassword("666888");//      user.setMoney(6000);//      user.setEmail("martin@qq.com");//      user.setDeptno(40);//      user.setId(1003);//      //      if(dao.update(user)){//          System.out.println("更新成功");//      }else{//          System.out.println("更新失败");//      }//      User user = new User();//      user.setName("marry");//      user.setPassword("123456");//      user.setMoney(5000);//      user.setEmail("marry@qq.com");//      user.setDeptno(30);//      System.out.println("id:"+user.getId());//      if(dao.save(user)){//          System.out.println("注册成功");//          System.out.println("您的id是:"+user.getId());//      }//      User user2 = dao.findById(1);//      System.out.println("欢迎你："+user2.getName());//      List<User> users = dao.findAll();//      for(User user : users){//          System.out.println("欢迎你："+user.getName());//      }    }}

package day03;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.Scanner;import day20151100.DBUtil2;public class PageDemo {    public static void main(String[] args) {        Scanner sc = new Scanner(System.in);        System.out.println("请输入要查看的表名");        String tableName = sc.nextLine().trim();        System.out.println("请输入排序的列名：");        String colName = sc.nextLine().trim();        System.out.println("请输入一页显示的条数");        int pagesize =             Integer.parseInt(sc.nextLine().trim());        System.out.println("请输入产看的页数");        int page =             Integer.parseInt(sc.nextLine().trim());        try{            Connection conn = DBUtil2.getConnection();            /*             * select * from (             *   select rownum rn,t.* from(             *      select * from t order by c             *   ) t             * )             * where rn between ? and ?             */            String sql = "select * from ("                    + "select rownum rn,t.* from("                    + "select * from "+tableName+" order by "+colName+" "                    + ") t"                    + " )"                    + " where rn between ? and ?";            PreparedStatement ps = conn.prepareStatement(sql);            //开始            int start = (page-1)*pagesize + 1;            //结束            int end = page*pagesize;            ps.setInt(1, start);            ps.setInt(2, end);            ResultSet rs = ps.executeQuery();            while(rs.next()){                int rw = rs.getInt(1);                int id = rs.getInt(2);                String name = rs.getString(3);                System.out.println(rw+","+id+","+name);            }        }catch(Exception e){        }finally{            DBUtil2.closeConnection();        }    }}

package day03;public class User {    private int id;    private String name;    private String password;    private int money;    private String email;    private int deptno;    public User() {        super();    }    public User(int id, String name, String password, int money, String email,            int deptno) {        super();        this.id = id;        this.name = name;        this.password = password;        this.money = money;        this.email = email;        this.deptno = deptno;    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }    public int getMoney() {        return money;    }    public void setMoney(int money) {        this.money = money;    }    public String getEmail() {        return email;    }    public void setEmail(String email) {        this.email = email;    }    public int getDeptno() {        return deptno;    }    public void setDeptno(int deptno) {        this.deptno = deptno;    }}

package day03;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import day20151100.DBUtil2;/** * UserDAO * 用于操作数据库user表的DAO * @author Administrator * */public class UserDAO {    private static final String FIND_BY_ID_SQL = "select * from user_ls where id=?";    private static final String FIND_ALL_SQL = "select * from user_ls";    private static final String SAVE_USER = "insert into user_ls values(user_seq_ls.nextval,?,?,?,?,?)";    private static final String UPDATE_SQL ="update user_ls set name=?,password=?,"            + "money=?,email=?,deptno=? where id=?";    private static final String DELETE_SQL="delete from user_ls where id=?";    /**     * 根据id查询对应的user记录     * @param id     * @return     */    public User findById(int id){        try{            Connection conn = DBUtil2.getConnection();            PreparedStatement ps = conn.prepareStatement(FIND_BY_ID_SQL);            ps.setInt(1, id);            ResultSet rs = ps.executeQuery();            if(rs.next()){                int i = rs.getInt("id");                String name = rs.getString("name");                String pwd = rs.getString("password");                int money = rs.getInt("money");                String email = rs.getString("email");                int deptno = rs.getInt("deptno");                User user = new User(i,name,pwd,money,email,deptno);                return user;            }            ps.close();        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }        return null;    }    /**     * 查询所有的user记录     * @return     */    public List<User> findAll(){        try{            Connection conn  = DBUtil2.getConnection();            Statement state = conn.createStatement();            ResultSet rs = state.executeQuery(FIND_ALL_SQL);            List<User> users = new ArrayList<User>();            while(rs.next()){                int i = rs.getInt("id");                String name = rs.getString("name");                String pwd = rs.getString("password");                int money = rs.getInt("money");                String email = rs.getString("email");                int deptno = rs.getInt("deptno");                User user = new User(i, name, pwd, money, email,deptno);                users.add(user);            }            state.close();            return users;        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }        return null;    }    /**     * 保存一个user信息     * @param user     * @return     */    public boolean save(User user){        try{            Connection conn = DBUtil2.getConnection();            PreparedStatement ps = conn.prepareStatement(                    SAVE_USER,new String[]{"id"});            ps.setString(1, user.getName());            ps.setString(2, user.getPassword());            ps.setInt(3, user.getMoney());            ps.setString(4, user.getEmail());            ps.setInt(5, user.getDeptno());            if(ps.executeUpdate()>0){                ResultSet rs = ps.getGeneratedKeys();                if(rs.next()){                    int id = rs.getInt(1);                    user.setId(id);                    ps.close();                    return true;                }            }            ps.close();        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }        return false;    }    /**     * 更新一个user信息     * @param user     * @return     */    public boolean update(User user){        /*         *"update user_ls set name=?,password=?,"            + "money=?,email=?,deptno=? where id=?";         */        try{            Connection conn = DBUtil2.getConnection();            PreparedStatement ps = conn.prepareStatement(UPDATE_SQL);            ps.setString(1, user.getName());            ps.setString(2, user.getPassword());            ps.setInt(3, user.getMoney());            ps.setString(4, user.getEmail());            ps.setInt(5, user.getDeptno());            ps.setInt(6, user.getId());            if(ps.executeUpdate()>0){                return true;            }            ps.close();        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }        return false;    }    /**     * 根据id删除一个user记录     * @param id     * @return     */    public boolean deleteById(int id){        /*         * delete from user_ls          * where id=?         */        try{            Connection conn = DBUtil2.getConnection();            PreparedStatement ps = conn.prepareStatement(DELETE_SQL);            ps.setInt(1, id);            if(ps.executeUpdate()>0){                return true;            }            ps.close();        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }        return false;    }}

package day03;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;import java.util.Scanner;import day20151100.DBUtil2;/** * 与用户相关的业务逻辑 */public class UserService {    public static void main(String[] args) {        /**         * 程序启动后         * 选择1、2、3、4等操作         * 1：注册新用户         * 2：更改用户信息         * 3：删除用户信息         * 4：查询用户信息         */        System.out.println("请输入选项：");        System.out.println("1：注册");        System.out.println("2：登陆");        System.out.println("3：修改");        System.out.println("4：删除");        System.out.println("5：查询");        System.out.println("6：转账");        Scanner sc = new Scanner(System.in);        int option = Integer.parseInt(sc.nextLine());        switch(option){        case 1:            //注册            regUser(sc);            break;        case 2:            login(sc);            break;        case 3:            modify(sc);            break;        case 4:            delete(sc);            break;        case 5:            query(sc);            break;        case 6:            giveMoney(sc);            break;        }    }    /**     * 修改密码     */    public static void modify(Scanner sc){        System.out.println("请输入要修改的账户：");        String name = sc.nextLine().trim();        System.out.println("请输入新密码：");        String pwd = sc.nextLine().trim();        try{            Connection conn = DBUtil2.getConnection();            String sql = "update user_ls set password =? where name=?";            PreparedStatement ps = conn.prepareStatement(sql);            ps.setString(1, pwd);            ps.setString(2, name);            if(ps.executeUpdate()>0){                System.out.println("修改密码成功");            }else{                System.out.println("用户不存在");            }            ps.close();        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }    }    /**     * 删除     * @param sc     */    public static void delete(Scanner sc){        /*         * 执行delete语句         * 判断成功与否         */        System.out.println("请输入要删除的账户：");        String user = sc.nextLine().trim();        try{            String sql = "delete from user_ls where "                    + "name=?";            System.out.println(sql);            Connection conn = DBUtil2.getConnection();            PreparedStatement ps = conn.prepareStatement(sql);            ps.setString(1, user);            if(ps.executeUpdate()>0){                System.out.println("删除成功");            }else{                System.out.println("账户不存在");            }            ps.close();        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }    }    /**     * 查询     * @param sc     */    public static void query(Scanner sc){        /*         * 1，输入用户名         * 2，显示用户信息         */        System.out.println("请输入要查询的账户：");        String user = sc.nextLine().trim();        try{            Connection conn = DBUtil2.getConnection();            String sql ="select id,name,password,money,email from user_ls "                    + "where name=?";            PreparedStatement ps = conn.prepareStatement(sql);            ps.setString(1, user);            ResultSet rs = ps.executeQuery();            if(rs.next()){                int id = rs.getInt("id");                String name = rs.getString("name");                String pwd = rs.getString("password");                int money = rs.getInt("money");                String email = rs.getString("email");                System.out.println(id+","+name+","+pwd+","+money+","+email);            }else{                System.out.println("用户不存在");            }            ps.close();        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }    }    /**     * 转账操作     */    public static void giveMoney(Scanner sc){        /*         * 1:获取用户输入的信息         * 2：必要的验证，看看转出账户余额是否够         * --事务开始的地方         * 3：更新转出账户的余额         * 4：更新转入账户的余额         * --提交事务完毕         */        System.out.println("现在是转账操作");        System.out.println("请输入您的账号");        String fromUser = sc.nextLine().trim();        System.out.println("请输入收款人账号");        String toUser = sc.nextLine().trim();        System.out.println("请输入转出金额");        String money = sc.nextLine().trim();        //2        String countSql="select money from user_ls"                + " where name='"+fromUser+"'";        try{            Connection conn = DBUtil2.getConnection();            //关闭自动提交事务            //开始纳入事务控制，因为开始执行DML操作了            conn.setAutoCommit(false);            Statement state = conn.createStatement();            ResultSet rs = state.executeQuery(countSql);            //判断是否查出数据            if(rs.next()){                int count = rs.getInt("money");                //判断余额是否够                if(count>=Integer.parseInt(money)){                    String fromSql = "update user_ls set money=money-"+money+" "                            + "where name='"+fromUser+"'";                    //修改汇款人余额                    if(state.executeUpdate(fromSql)>0){                        //修改收款人余额                        String toSql = "update user_ls set money=money+"+money+" "                                + "where name='"+toUser+"'";                        if(state.executeUpdate(toSql)>0){                            System.out.println("转账成功");                            /*                             * 2次更新账户操作均成功，我们才认为                             * 这次转账操作完毕。提交事务                             */                            conn.commit();                        }else{                            /*                             * 若第二次更新操作失败，那么整次                             * 操作就算失败，应该回滚事务                             */                            System.out.println("转账失败");                            conn.rollback();                        }                    }                }else{                    System.out.println("余额不足");                }            }else{                System.out.println("没有该用户"+fromUser);            }        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }    }    /**     * 注册操作     * @param sc     */    public static void regUser(Scanner sc){        /*         * 若是注册：         * 1：获取用户输入的信息         * 2：获取连接         * 3：获取statement         * 4：先获取id的最大值         * 5：对该值+1，作为当前记录的主键值         * 6：插入记录         * 7：关闭连接         */          try{              //1              System.out.println("现在是注册操作：");              System.out.println("请输入用户名：");              String user = sc.nextLine().trim();              System.out.println("请输入密码：");              String pwd = sc.nextLine().trim();              System.out.println("momey:");              String money = sc.nextLine().trim();              System.out.println("mail:");              String email = sc.nextLine().trim();              //2              Connection conn = DBUtil2.getConnection();              //3              Statement state = conn.createStatement();              //4              String sql = "select max(id) id from user_ls";              //5              ResultSet rs = state.executeQuery(sql);              int id = -1;              if(rs.next()){                  id = rs.getInt("id");              }              System.out.println("当前最大id是:"+id);              //统计处最大值后对id加1              id++;              rs.close();              //6              /*               * insert into user_ls                * values(1,'jack','1234',15000,'obzdff@163.com')               * 使用变量格式："+变量名+"               */              sql = "insert into user_ls values" +                    "("+id+",'"+user+"','"+pwd+"',"+money+",'"+email+"')";              System.out.println(sql);              if(state.executeUpdate(sql)>0){                  System.out.println("注册成功，欢迎你"+user);              }else{                  System.out.println("呵呵");              }          }catch(Exception e){              e.printStackTrace();          }finally{              DBUtil2.closeConnection();          }    }    /*     * 登陆     */    public static void login(Scanner sc){        /*         * 1：要求输入用户名密码         * 2：去表中查         * 3：查到，对的         */        System.out.println("现在是登陆操作：");        System.out.println("输入用户名：");        String user = sc.nextLine().trim();        System.out.println("pwd:");        String pwd = sc.nextLine().trim();        try{            Connection conn = DBUtil2.getConnection();            /*             * Statement的不安全因素：             * 假设用户名任意，密码：a' OR '1'='1             * select * from user_ls              * where LOWER(name)=LOWER('wewq')              * and password = 'a' OR '1'='1'             * 可以登陆成功             */            //Statement state = conn.createStatement();            //String sql = "select * from user_ls " +            //      "where LOWER(name)=LOWER('"+user+"') and password = '"+pwd+"'";            String sql = "select * from user_ls where " +                    "name=? and password=?";            PreparedStatement ps = conn.prepareStatement(sql);            ps.setString(1, user);            ps.setString(2, pwd);            ResultSet rs = ps.executeQuery();            //ResultSet rs = state.executeQuery(sql);            //根据用户输入能否查到数据            if(rs.next()){                System.out.println("登录成功");            }else{                System.out.println("用户名或密码错误");            }//          if(state.execute(sql)){//              System.out.println("");//          }        }catch(Exception e){            e.printStackTrace();        }finally{            DBUtil2.closeConnection();        }    }}

0 0