JDBC1

---恢复内容开始---

create table `account` (
    `id` int (11),
    `name` char (60),
    `balance` int (11)
); 
insert into `account` (`id`, `name`, `balance`) values('1','aa','2000');
insert into `account` (`id`, `name`, `balance`) values('2','bb','2000');
insert into `account` (`id`, `name`, `balance`) values('3','王五','2000');

1.用jdbc设置aa的balance值为1500

public class jdbcdemo1 {
    public static void main(string[] args) throws exception {

        //1. 导入驱动jar包
        //2.注册驱动
        class.forname("com.mysql.jdbc.driver");
        //3.获取数据库连接对象
        connection conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
        //4.定义sql语句
       string sql = "update account set balance = 1500 where id = 1";
        //5.获取执行sql的对象 statement
        statement stmt = conn.createstatement();
        //6.执行sql
        int count = stmt.executeupdate(sql);
        //7.处理结果
        system.out.println(count);
        //8.释放资源
        stmt.close();
        conn.close();

    }
}

2.用jdbc添加姓名cc,balance为3000

public class jdbcdemo2 {
    public static void main(string[] args) {
        statement stmt = null;
        connection conn = null;
        try {
            //1. 注册驱动
            class.forname("com.mysql.jdbc.driver");
            //2. 定义sql
            string sql = "insert into account values(null,'cc',3000)";
            //3.获取connection对象
            conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
            //4.获取执行sql的对象 statement
            stmt = conn.createstatement();
            //5.执行sql
            int count = stmt.executeupdate(sql);//影响的行数
            //6.处理结果
            system.out.println(count);
            if(count > 0){
                system.out.println("添加成功！");
            }else{
                system.out.println("添加失败！");
            }

        } catch (classnotfoundexception e) {
            e.printstacktrace();
        } catch (sqlexception e) {
            e.printstacktrace();
        }finally {
            //stmt.close();
            //7. 释放资源
            //避免空指针异常
            if(stmt != null){
                try {
                    stmt.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }
        }


    }
}

3.用jdbc删除id为3的数据

public class jdbcdemo3 {
    public static void main(string[] args) {
        connection conn = null;
        statement stmt = null;
        try {
            //1. 注册驱动
            class.forname("com.mysql.jdbc.driver");
            //2.获取连接对象
            conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
           //conn = jdbcutils.getconnection("jdbc:mysql:///db3", "root", "root");
            //3.定义sql
            string sql  = "delete from account where id = 3";
            //4.获取执行sql对象
            stmt = conn.createstatement();
            //5.执行sql
            int count = stmt.executeupdate(sql);
            //6.处理结果
            system.out.println(count);
            if(count > 0){
                system.out.println("删除成功！");
            }else{
                system.out.println("删除失败");
            }

        } catch (classnotfoundexception e) {
            e.printstacktrace();
        } catch (sqlexception e) {
            e.printstacktrace();
        } finally {
            //7.释放资源

            if(stmt != null){
                try {
                    stmt.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }
        }
    }
}

4.用jdbc创建一个student表

/**
 * 执行ddl语句
 */
public class jdbcdemo5 {
    public static void main(string[] args) {
        connection conn = null;
        statement stmt = null;
        try {
            //1. 注册驱动
            class.forname("com.mysql.jdbc.driver");
            //2.获取连接对象
            conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
            //3.定义sql
            string sql  = "create table student1 (id int , name varchar(20))";
            //4.获取执行sql对象
            stmt = conn.createstatement();
            //5.执行sql
            int count = stmt.executeupdate(sql);
            //6.处理结果
            system.out.println(count);

        } catch (classnotfoundexception e) {
            e.printstacktrace();
        } catch (sqlexception e) {
            e.printstacktrace();
        } finally {
            //7.释放资源

            if(stmt != null){
                try {
                    stmt.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }
        }
    }

}

5.用jdbc查询account表中所有数据

/**
 * 执行ddl语句
 */
public class jdbcdemo7 {
    public static void main(string[] args) {
        connection conn = null;
        statement stmt = null;
        resultset rs = null;
        try {
            //1. 注册驱动
            class.forname("com.mysql.jdbc.driver");
            //2.获取连接对象
            conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
            //3.定义sql
            string sql  = "select * from account";
            //4.获取执行sql对象
            stmt = conn.createstatement();
            //5.执行sql
            rs = stmt.executequery(sql);
            //6.处理结果
            //循环判断游标是否是最后一行末尾。
            while(rs.next()){

                //获取数据
                //6.2 获取数据
                int id = rs.getint(1);
                string name = rs.getstring("name");
                double balance = rs.getdouble(3);

                system.out.println(id + "---" + name + "---" + balance);
            }


           /* //6.1 让游标向下移动一行
            if(rs.next()){
                //判断是否有数据
                //6.2 获取数据
                int id = rs.getint(1);
                string name = rs.getstring("name");
                double balance = rs.getdouble(3);

                system.out.println(id + "---" + name + "---" + balance);
            }

            //6.1 让游标向下移动一行
            if(rs.next()){
                //判断是否有数据
                //6.2 获取数据
                int id = rs.getint(1);
                string name = rs.getstring("name");
                double balance = rs.getdouble(3);

                system.out.println(id + "---" + name + "---" + balance);
            }

            //6.1 让游标向下移动一行
            if(rs.next()){
                //判断是否有数据
                //6.2 获取数据
                int id = rs.getint(1);
                string name = rs.getstring("name");
                double balance = rs.getdouble(3);

                system.out.println(id + "---" + name + "---" + balance);
            }

            //6.1 让游标向下移动一行
            if(rs.next()){
                //判断是否有数据
                //6.2 获取数据
                int id = rs.getint(1);
                string name = rs.getstring("name");
                double balance = rs.getdouble(3);

                system.out.println(id + "---" + name + "---" + balance);
            }*/

          /*  //6.1 让游标向下移动一行
            rs.next();
            //6.2 获取数据
            int id2 = rs.getint(1);
            string name2 = rs.getstring("name");
            double balance2 = rs.getdouble(3);

            system.out.println(id2 + "---" + name2 + "---" + balance2);

            //6.1 让游标向下移动一行
            rs.next();
            //6.2 获取数据
            int id3 = rs.getint(1);
            string name3 = rs.getstring("name");
            double balance3 = rs.getdouble(3);

            system.out.println(id3 + "---" + name3 + "---" + balance3);*/


        } catch (classnotfoundexception e) {
            e.printstacktrace();
        } catch (sqlexception e) {
            e.printstacktrace();
        } finally {
            //7.释放资源

            if(rs != null){
                try {
                    rs.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }

            if(stmt != null){
                try {
                    stmt.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }
        }
    }

}

6.用jdbc查询account表中所有数据

public class emp {
    private int id;
    private string ename;
    private int job_id;
    private int mgr;
    private date joindate;
    private double salary;
    private double bonus;
    private int dept_id;


    public int getid() {
        return id;
    }

    public void setid(int id) {
        this.id = id;
    }

    public string getename() {
        return ename;
    }

    public void setename(string ename) {
        this.ename = ename;
    }

    public int getjob_id() {
        return job_id;
    }

    public void setjob_id(int job_id) {
        this.job_id = job_id;
    }

    public int getmgr() {
        return mgr;
    }

    public void setmgr(int mgr) {
        this.mgr = mgr;
    }

    public date getjoindate() {
        return joindate;
    }

    public void setjoindate(date joindate) {
        this.joindate = joindate;
    }

    public double getsalary() {
        return salary;
    }

    public void setsalary(double salary) {
        this.salary = salary;
    }


    public int getdept_id() {
        return dept_id;
    }

    public void setdept_id(int dept_id) {
        this.dept_id = dept_id;
    }


    public double getbonus() {
        return bonus;
    }

    public void setbonus(double bonus) {
        this.bonus = bonus;
    }


    @override
    public string tostring() {
        return "emp{" +
                "id=" + id +
                ", ename='" + ename + '\'' +
                ", job_id=" + job_id +
                ", mgr=" + mgr +
                ", joindate=" + joindate +
                ", salary=" + salary +
                ", bonus=" + bonus +
                ", dept_id=" + dept_id +
                '}';
    }
}

public class jdbcdemo8 {

    public static void main(string[] args) {
        list<emp> list = new jdbcdemo8().findall();
        system.out.println(list);
        system.out.println(list.size());
    }
    /**
     * 查询所有emp对象
     * @return
     */
    public list<emp> findall(){
        connection conn = null;
        statement stmt = null;
        resultset rs = null;
        list<emp> list = null;
        try {
            //1.注册驱动
            class.forname("com.mysql.jdbc.driver");
            //2.获取连接
            conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");
            //3.定义sql
            string sql = "select * from emp";
            //4.获取执行sql的对象
            stmt = conn.createstatement();
            //5.执行sql
            rs = stmt.executequery(sql);
            //6.遍历结果集，封装对象，装载集合
            emp emp = null;
            list = new arraylist<emp>();
            while(rs.next()){
                //获取数据
                int id = rs.getint("id");
                string ename = rs.getstring("name");
                string ename = rs.getstring("");
                date joindate = rs.getdate("joindate");
                double salary = rs.getdouble("salary");

                int dept_id = rs.getint("dept_id");
                // 创建emp对象,并赋值
                emp = new emp();
                emp.setid(id);
                emp.setename(ename);
                emp.setjob_id(job_id);
                emp.setmgr(mgr);
                emp.setjoindate(joindate);
                emp.setsalary(salary);
                emp.setbonus(bonus);
                emp.setdept_id(dept_id);

                //装载集合
                list.add(emp);
            }

        } catch (classnotfoundexception e) {
            e.printstacktrace();
        } catch (sqlexception e) {
            e.printstacktrace();
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }

            if(stmt != null){
                try {
                    stmt.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }
        }
        return list;
    }


    /**
     * 演示jdbc工具类
     * @return
     */
    public list<emp> findall2(){
        connection conn = null;
        statement stmt = null;
        resultset rs = null;
        list<emp> list = null;
        try {
           /* //1.注册驱动
            class.forname("com.mysql.jdbc.driver");
            //2.获取连接
            conn = drivermanager.getconnection("jdbc:mysql:///db3", "root", "root");*/
            conn = jdbcutils.getconnection();
            //3.定义sql
            string sql = "select * from emp";
            //4.获取执行sql的对象
            stmt = conn.createstatement();
            //5.执行sql
            rs = stmt.executequery(sql);
            //6.遍历结果集，封装对象，装载集合
            emp emp = null;
            list = new arraylist<emp>();
            while(rs.next()){
                //获取数据
                int id = rs.getint("id");
                string ename = rs.getstring("ename");
                int job_id = rs.getint("job_id");
                int mgr = rs.getint("mgr");
                date joindate = rs.getdate("joindate");
                double salary = rs.getdouble("salary");
                double bonus = rs.getdouble("bonus");
                int dept_id = rs.getint("dept_id");
                // 创建emp对象,并赋值
                emp = new emp();
                emp.setid(id);
                emp.setename(ename);
                emp.setjob_id(job_id);
                emp.setmgr(mgr);
                emp.setjoindate(joindate);
                emp.setsalary(salary);
                emp.setbonus(bonus);
                emp.setdept_id(dept_id);

                //装载集合
                list.add(emp);
            }

        } catch (sqlexception e) {
            e.printstacktrace();
        }finally {
            /*if(rs != null){
                try {
                    rs.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }

            if(stmt != null){
                try {
                    stmt.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (sqlexception e) {
                    e.printstacktrace();
                }
            }*/

            jdbcutils.close(rs,stmt,conn);
        }
        return list;
    }

}

7.jdbc工具类

/**
 * jdbc工具类
 */
public class jdbcutils {
    private static string url;
    private static string user;
    private static string password;
    private static string driver;
    /**
     * 文件的读取，只需要读取一次即可拿到这些值。使用静态代码块
     */
    static{
        //读取资源文件，获取值。

        try {
            //1. 创建properties集合类。
            properties pro = new properties();

            //获取src路径下的文件的方式--->classloader 类加载器
            classloader classloader = jdbcutils.class.getclassloader();
            url res  = classloader.getresource("jdbc.properties");
            string path = res.getpath();
           // system.out.println(path);///d:/ideaprojects/itcast/out/production/day04_jdbc/jdbc.properties
            //2. 加载文件
           // pro.load(new filereader("d:\\ideaprojects\\itcast\\day04_jdbc\\src\\jdbc.properties"));
            pro.load(new filereader(path));

            //3. 获取数据，赋值
            url = pro.getproperty("url");
            user = pro.getproperty("user");
            password = pro.getproperty("password");
            driver = pro.getproperty("driver");
            //4. 注册驱动
            class.forname(driver);
        } catch (ioexception e) {
            e.printstacktrace();
        } catch (classnotfoundexception e) {
            e.printstacktrace();
        }
    }


    /**
     * 获取连接
     * @return 连接对象
     */
    public static connection getconnection() throws sqlexception {

        return drivermanager.getconnection(url, user, password);
    }

    /**
     * 释放资源
     * @param stmt
     * @param conn
     */
    public static void close(statement stmt,connection conn){
        if( stmt != null){
            try {
                stmt.close();
            } catch (sqlexception e) {
                e.printstacktrace();
            }
        }

        if( conn != null){
            try {
                conn.close();
            } catch (sqlexception e) {
                e.printstacktrace();
            }
        }
    }


    /**
     * 释放资源
     * @param stmt
     * @param conn
     */
    public static void close(resultset rs,statement stmt, connection conn){
        if( rs != null){
            try {
                rs.close();
            } catch (sqlexception e) {
                e.printstacktrace();
            }
        }

        if( stmt != null){
            try {
                stmt.close();
            } catch (sqlexception e) {
                e.printstacktrace();
            }
        }

        if( conn != null){
            try {
                conn.close();
            } catch (sqlexception e) {
                e.printstacktrace();
            }
        }
    }

}

8.通过键盘录入用户名和密码，判断用户是否登录成功

/**
 * 练习：
 *         * 需求：
 *             1. 通过键盘录入用户名和密码
 *             2. 判断用户是否登录成功
 */
public class jdbcdemo9 {

    public static void main(string[] args) {
        //1.键盘录入，接受用户名和密码
        scanner sc = new scanner(system.in);
        system.out.println("请输入用户名：");
        string username = sc.nextline();
        system.out.println("请输入密码：");
        string password = sc.nextline();
        //2.调用方法
        boolean flag = new jdbcdemo9().login2(username, password);
        //3.判断结果，输出不同语句
        if(flag){
            //登录成功
            system.out.println("登录成功！");
        }else{
            system.out.println("用户名或密码错误！");
        }


    }



    /**
     * 登录方法
     */
    public boolean login(string username ,string password){
        if(username == null || password == null){
            return false;
        }
        //连接数据库判断是否登录成功
        connection conn = null;
        statement stmt =  null;
        resultset rs = null;
        //1.获取连接
        try {
            conn =  jdbcutils.getconnection();
            //2.定义sql
            string sql = "select * from user where username = '"+username+"' and password = '"+password+"' ";
            system.out.println(sql);
            //3.获取执行sql的对象
            stmt = conn.createstatement();
            //4.执行查询
            rs = stmt.executequery(sql);
            //5.判断
           /* if(rs.next()){//如果有下一行，则返回true
                return true;
            }else{
                return false;
            }*/
           return rs.next();//如果有下一行，则返回true

        } catch (sqlexception e) {
            e.printstacktrace();
        }finally {
            jdbcutils.close(rs,stmt,conn);
        }


        return false;
    }

    /**
     * 登录方法,使用preparedstatement实现
     */
    public boolean login2(string username ,string password){
        if(username == null || password == null){
            return false;
        }
        //连接数据库判断是否登录成功
        connection conn = null;
        preparedstatement pstmt =  null;
        resultset rs = null;
        //1.获取连接
        try {
            conn =  jdbcutils.getconnection();
            //2.定义sql
            string sql = "select * from user where username = ? and password = ?";
            //3.获取执行sql的对象
            pstmt = conn.preparestatement(sql);
            //给?赋值
            pstmt.setstring(1,username);
            pstmt.setstring(2,password);
            //4.执行查询,不需要传递sql
            rs = pstmt.executequery();
            //5.判断
           /* if(rs.next()){//如果有下一行，则返回true
                return true;
            }else{
                return false;
            }*/
            return rs.next();//如果有下一行，则返回true

        } catch (sqlexception e) {
            e.printstacktrace();
        }finally {
            jdbcutils.close(rs,pstmt,conn);
        }


        return false;
    }


}

9.jdbc事务操作

/**
 * 事务操作
 */
public class jdbcdemo10 {


    public static void main(string[] args) {
        connection conn = null;
        preparedstatement pstmt1 = null;
        preparedstatement pstmt2 = null;

        try {
            //1.获取连接
            conn = jdbcutils.getconnection();
            //开启事务
            conn.setautocommit(false);

            //2.定义sql
            //2.1 张三 - 500
            string sql1 = "update account set balance = balance - ? where id = ?";
            //2.2 李四 + 500
            string sql2 = "update account set balance = balance + ? where id = ?";
            //3.获取执行sql对象
            pstmt1 = conn.preparestatement(sql1);
            pstmt2 = conn.preparestatement(sql2);
            //4. 设置参数
            pstmt1.setdouble(1,500);
            pstmt1.setint(2,1);

            pstmt2.setdouble(1,500);
            pstmt2.setint(2,2);
            //5.执行sql
            pstmt1.executeupdate();
            // 手动制造异常
            int i = 3/0;

            pstmt2.executeupdate();
            //提交事务
            conn.commit();
        } catch (exception e) {
            //事务回滚
            try {
                if(conn != null) {
                    conn.rollback();
                }
            } catch (sqlexception e1) {
                e1.printstacktrace();
            }
            e.printstacktrace();
        }finally {
            jdbcutils.close(pstmt1,conn);
            jdbcutils.close(pstmt2,null);
        }


    }

}

 

---恢复内容结束---