JDBC(4)

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();
        }
    }



}