JDBC连接数据库

一、登录                                                 二、数据库的一般操作                                                   三、使用工具类连接                        四、数据库连接需要的数据

……………………………………………………………………………………………………………………………………………………………………………………………………………………

一、登录

实例一：

//实体类对象
package com.qf.login;

public class User {
    private int id;
    private String username;
    private String password;
}

//登录
package com.qf.login;

//从控制台输入用户名和密码，判断登录是否成功
public class Login {
    public static void main(String[] args) {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();

        // 封装对象
        User user = new User();
        user.setUsername(username);
        user.setPassword(password);

        // 调用具体的业务处理类
        DoLogin dl = new DoLogin();
        User u = dl.findUser(user);
        if (u == null) {
            System.out.println("登录失败");
        } else {
            System.out.println("欢迎" + u.getUsername() + "登录成功");
        }
    }
}

//处理事务
package com.qf.login;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DoLogin {      
    public User findUser(User u) {
        User user = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/javaee02", "root", "admin");

            Statement stmt = conn.createStatement();
            String sql = "select * from user where username='" + u.getUsername() + "' and password='" + u.getPassword()
                    + "'";

            ResultSet rs = stmt.executeQuery(sql);
            if (rs.next()) {
                user = new User();
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return user;
    }
}

实例二：

package com.neusoft.oracle.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class Test3 {
    public static void main(String[] args) {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String oravleUr1="jdbc:oracle:thin:@//localhost:1521/neusoft";
        String user = "scott";
        String Password="tiger";
//        String sql  = "select * from user1 "
//                      +"where username = ? "
//                      +"and password = ?";
        StringBuilder sb = new StringBuilder();
        sb.append("select * ");
        sb.append("from user1 ");
        sb.append("where username = ? ");
        sb.append("and password = ?");
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(oravleUr1, user, Password);
            //PreparedStatement pstmt = conn.prepareStatement(sql);
            PreparedStatement pstmt = conn.prepareStatement(sb.toString());
            Scanner sc = new Scanner(System.in);
            System.out.println("please input username");
            String username = sc.nextLine();
            System.out.println("please input password");
            int  password1 = sc.nextInt();
            pstmt.setString(1, username);
            pstmt.setInt(2, password1);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()){
                System.out.println("登陆成功");
            }else{
                System.out.println("登录失败");
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } 
    }

二、数据库的一般操作

（一）JDBC:连接数据库     （二）JDBC:增删改查    （三）SQL语句注入的区别         （四）遍历结果集封装对象存储到list结合         （五）Junit简单使用

（一）JDBC:连接数据库【使用API文档】

1.需要jar包支持。  驱动直接去jar里找。

2.步骤。

public class DemoJdbc01 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // a:加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        // DriverManager.registerDriver(new com.mysql.jdbc.Driver());

        // b:创建连接
        // conn =
        // DriverManager.getConnection("jdbc:mysql://localhost:3306/javaee02",
        // "root", "admin");

        // conn =
        // DriverManager.getConnection("jdbc:mysql://localhost:3306/javaee02?user=root&password=admin");

        Properties info = new Properties();
        info.setProperty("user", "root");      //封装用户名和属性
        info.setProperty("password", "admin");  //名值对
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/javaee02", info);

        // c:创建执行sql语句的Statement对象
        stmt = conn.createStatement();

        // d:执行sql语句并处理结果
        String sql = "select * from emp";

        // 执行查询语句返回结果集
        rs = stmt.executeQuery(sql);

        // 结果集光标默认在第一条记录上方，调用next()光标向下移动一行
        // 如果有新行，返回true；如果到达末尾返回false
        while (rs.next()) {
               //使用get方法获得
                String ename = rs.getString(2);// 字段索引，从1开始
                int sal = rs.getInt("sal");// 字段名
                System.out.println(ename + "," + sal);
        }
        } catch (Exception e) {
        e.printStackTrace();
        } finally {
            // e:关闭资源
        try {
                if (rs != null) {
            rs.close();
                }
                if (stmt != null)
            stmt.close();
                if (conn != null) {
            conn.close();
                }
                } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            }
        }
    }
}

next()将光标从当前位置向前移一行。

返回：如果新的当前行有效，则返回true；如果不存在下一行，则返回false。

（二）JDBC:增删改查

package com.qf.jdbc;

import org.junit.Test;

public class DemoJdbc02 {
    @Test
    public void testInsert01() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/javaee02", "root", "admin");
        Statement stmt = conn.createStatement();

        String sql = "insert into student(name,age,sex,birthday,score,description) "
                + "values('tom',20,'M','1999-9-9',99.5,'tom is good boy')";

        // 返回值为影响表的行数
        int n = stmt.executeUpdate(sql);
        if (n > 0) {
            System.out.println("success");
        }
    }

    @Test
    public void testInsert02() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/javaee02", "root", "admin");
        Statement stmt = conn.createStatement();

        String name = "jack";
        int age = 21;
        String date = "2000-9-9";

        String sql = "insert into student(name,age,sex,birthday,score,description) " + "values('" + name + "'," + age
                + ",'M','" + date + "',99.5,'tom is good boy')";

        // 返回值为影响表的行数
        int n = stmt.executeUpdate(sql);
        if (n > 0) {
            System.out.println("success");
        }
    }

    @Test
    public void testUpdate() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/javaee02", "root", "admin");
        Statement stmt = conn.createStatement();

        String sql = "update student set score=90 where id=2";

        // 返回值为影响表的行数
        int n = stmt.executeUpdate(sql);
        // System.out.println(n);
        if (n > 0) {
            System.out.println("success");
        }
    }

    @Test
    public void testDelete() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/javaee02", "root", "admin");
        Statement stmt = conn.createStatement();

        String sql = "delete from student where id=2";

        // 返回值为影响表的行数
        int n = stmt.executeUpdate(sql);
        if (n > 0) {
            System.out.println("success");
        }
    }

    @Test
    public void testCount() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/javaee02", "root", "admin");
        Statement stmt = conn.createStatement();

        String sql = "select count(*) c from emp";

        ResultSet rs = stmt.executeQuery(sql);
        if (rs.next())
            // System.out.println(rs.getInt(1));
            System.out.println(rs.getInt("c"));
    }
}

 

（三）SQL语句注入的区别

statement和preparedstatement的区别：

package com.neusoft.oracle.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Test2 {
    public static void main(String[] args) {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String oracleUrl = "jdbc:oracle:thin:@//localhost:1521/neusoft";
        String user = "scott";
        String password = "tiger";
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(oracleUrl, user, password);
            Statement stmt = conn.createStatement();
            Scanner sc = new Scanner(System.in);
            System.out.println("please input username");
            String username = sc.nextLine();
            System.out.println("please input password");
            String password1 = sc.nextLine();
            
            //sql注入
            String sql = "select * "
                    + "from user1 "
                    + "where username = '"+username+"' "
                    + "and password = "+password1;
            System.out.println(sql);
            ResultSet rs = stmt.executeQuery(sql);
            if(rs.next()){
                System.out.println("登陆成功");
            }else{
                System.out.println("登陆失败");
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

 

package com.neusoft.oracle.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AnotherTest {
    public static void main(String[] args) {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String oracleUrl = "jdbc:oracle:thin:@//localhost:1521/neusoft";
        String user = "scott";
        String password = "tiger";
        int empno1 = 7369;
        String ename1 = "SMITH";
        String sql = "select * "
                + "from emp "
                + "where empno = ? "
                + "and ename = ?";
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(oracleUrl, user, password);
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, empno1);
            pstmt.setString(2, ename1);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()){
                System.out.println(rs.getInt("empno"));
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

 

（四）遍历结果集封装对象存储到list结合

//实体类，get，set，tostring
package com.qf.entity;

public class Student {
    private int id;
    private String name;
    private int age;
    private String sex;
    private String birthday;
    private double score;
    private String description;    
}

package com.qf.jdbc;

public class DemoJdbc03 {
    public static void main(String[] args) {
        DemoJdbc03 dj = new DemoJdbc03();
        System.out.println(dj.findAll());
    }

    public List<Student> findAll() {
        List<Student> list = new ArrayList<Student>();

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/javaee02", "root", "admin");
            stmt = conn.createStatement();
            String sql = "select * from student";
            rs = stmt.executeQuery(sql);
            // 遍历结果集
            while (rs.next()) {
                //封装对象
                Student stu = new Student();
                stu.setId(rs.getInt("id"));
                stu.setName(rs.getString("name"));
                stu.setAge(rs.getInt("age"));
                stu.setBirthday(rs.getString("birthday"));
                stu.setSex(rs.getString("sex"));
                stu.setDescription(rs.getString("description"));

                // 将对象存储到集合中
                list.add(stu);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // e:关闭资源
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null)
                    stmt.close();
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return list;
    }
}

 

（五）Junit简单实用

package com.qf.junit;

public class Cal {
    public int add(int a, int b) {
        return a + b;
    }

    public int mul(int a, int b) {
        return a * b;
    }
}

package com.qf.junit;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import junit.framework.Assert;

public class Demo {

    // 单元测试的方法是无参数无返回值

    @Before  //Junit执行之前执行
    public void before() {
        System.out.println("before");
    }

    @SuppressWarnings("deprecation")
    @Test // 注解。也就是说这是一个单元测试方法
    public void testAdd() {
        Cal cal = new Cal();
        // 断言：需要判断方法的返回值与预期的值是否一致
        Assert.assertEquals(3, cal.add(1, 2));
    }

    @After
    public void after() {
        System.out.println("after");
    }
}

 

三、使用工具类连接

（一）工具类的使用　　　　　　（二）封装数据库工具类　　　　　　（三）分页　　　　　　（四）转账

（一）工具类的使用

//连接数据库，进行增删改查
package com.qf.jdbc;

public class Demo01 {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtils.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from emp");
            while (rs.next()) {
                System.out.println(rs.getString("ename"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(rs, stmt, conn);
        }
    }
}

为了解决sql注入：  ('jgh' or '1'='1' )，只要有or则前面的被后面的覆盖掉，成功运行。所以statement改用preparedstatement，将不会出现这种失误。

package com.qf.jdbc;

public class Demo02 {

    @Test     //数据库的增删改一样
    public void testInsert() {
        try {
            Connection conn = DBUtils.getConnection();
            //创建PreparedStatement对象，封装SQL语句
                   String sql = "insert into user(username,password) values(?,?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            //给上面的问号赋值。参数索引从1开始
                   pstmt.setString(1, "abc");
            pstmt.setString(2, "def");
                               
            int n = pstmt.executeUpdate();
            if (n > 0) {
                System.out.println("success");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
 
     @Test
    public void testUpdate() {
        try {
            Connection conn = DBUtils.getConnection();
            String sql = "update user set username=?,password=? where id=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "aaa");
            pstmt.setString(2, "bbb");
            pstmt.setInt(3, 3);

            int n = pstmt.executeUpdate();
            if (n > 0) {
                System.out.println("success");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
 
     @Test
    public void testDelete() {
        try {
            Connection conn = DBUtils.getConnection();
            String sql = " delete from user where id=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, 3);

            int n = pstmt.executeUpdate();
            if (n > 0) {
                System.out.println("success");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}

 

（二）封装数据库工具类

1.第一种方法

在同包下建立feil文件，输入下列代码：

//db.properties
driver = com.mysql.jdbc.Driver
mysqlUrl = jdbc:mysql://localhost:3306/RUNOOB
user = root
password = root

package com.neusoft.oracle.jdbc;

public class DBUtil {
    private static String driver = null;
    private static String mysqlUrl = null;
    private static String user = null;
    private static String password = null;
    private static Connection conn = null;
    static{
        try {
            Properties p = new Properties();
            p.load(DBUtil.class.getResourceAsStream("db.properties"));
            driver = p.getProperty("driver");
            mysqlUrl = p.getProperty("mysqlUrl");
            user = p.getProperty("user");
            password = p.getProperty("password");
            Class.forName(driver);
            conn = DriverManager.getConnection(mysqlUrl, user, password);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            }catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }     
    }
    private DBUtil(){
        
    }
    public static Connection getConnection(){
        return conn;    
    }
}
//关闭一个流
public static void close(ResultSet rs,PreparedStatement pstmt,Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(pstmt != null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}

 

2.第二种方法

配置文件
DBConfig.properties //文件名
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/java
username=root
password=admin

DBUtil.java //文件名
package com.qf.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

//数据库工具类
//获得配置文件内容，创建获得连接的方法，关闭资源的方法
public class DBUtils {
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;

    static {
        // 读取配置文件信息
        ResourceBundle rb = ResourceBundle.getBundle("DBConfig");
        driverClass = rb.getString("driverClass");
        url = rb.getString("url");
        username = rb.getString("username");
        password = rb.getString("password");

        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    // 获得连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    // 关闭资源
    public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
        try {
            if (rs != null)
                rs.close();
            if (stmt != null)
                stmt.close();
            if (conn != null)
                conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}

 

（三）分页

分页：数据量大时需要分页。

select * from emp limit 起始行索引，长度（个数）

 

1.规定每页显示的记录数

pageSize=3;

2.获得表中的总记录数

total

select count(*) from emp

3.计算页数

pageCount

total%pageSize==0?total%pageSize:total%pageSize+1

Math.ceiling(total*1.0/pageSize);

4.获得每页显示的数据

select * from emp limit 0,3

select * from emp limit 3,3

select * from emp limit 6,3

　　获得每页数据，只需要获得页码（pageNo  1,2,3……）即可

select * from emp limit (pageNo-1)*pageSize,pageSize;

创建实体类
package com.qf.entity;

public class Emp {
    private int empno;
    private String ename;
    private int sal;

    @Override
    public String toString() {
        return "Emp [empno=" + empno + ", ename=" + ename + ", sal=" + sal + "]";
    }
}

package com.qf.page;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Scanner;

import com.qf.entity.Emp;
import com.qf.utils.DBUtils;

public class DemoPage {

    private static int pageSize = 3;// 每页显示的记录数
    private static int total;// 总记录数
    private static int pageCount;// 总页数

    public static void main(String[] args) {
        // 首先打印页码，以及第一页内容
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = DBUtils.getConnection();
            String sql = "select count(*) from emp";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                total = rs.getInt(1); //获得总的记录数
            }
            // 计算总页数
            pageCount = (int) Math.ceil(total * 1.0 / pageSize);

            // 打印页码
            for (int i = 1; i <= pageCount; i++) {
                System.out.print(i + " ");
            }
            System.out.println();

            int pageNo = 1;
            // 显示第一页数据
            print(pageNo);

            System.out.println("请输入页码");
            Scanner sc = new Scanner(System.in);
            pageNo = Integer.parseInt(sc.next());

            if (pageNo <= pageCount && pageNo > 0)
                print(pageNo);
            else {
                System.out.println("请输入正确页码");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    /根据页码获得每页数据，打印数据
     * @param pageNo
     */
    private static void print(int pageNo) {
        List<Emp> list = getData(pageNo);
        Iterator<Emp> it = list.iterator();
        while (it.hasNext()) {
            System.out.println(it.next());
        }
    }

    /**
     * 封装方法，获得每页的数据
     * @param pageNo,页码
     * @return
     */
    public static List<Emp> getData(int pageNo) {
        List<Emp> list = new ArrayList<Emp>();
        try {
            Connection conn = DBUtils.getConnection();
            String sql = "select empno,ename,sal from emp limit ?,?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, (pageNo - 1) * pageSize);
            pstmt.setInt(2, pageSize);

            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Emp emp = new Emp();
                emp.setEmpno(rs.getInt(1));
                emp.setEname(rs.getString(2));
                emp.setSal(rs.getInt(3));

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

 

（四）转账

package com.neusoft.oracle.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestConn {
    public static void main(String[] args) {    
        Connection conn = OracleDBUtil.getConnection();
        try {
            conn.setAutoCommit(false);
            give(conn);
            recieve(conn);
            conn.commit();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        }
    }
    public static void give(Connection conn) throws SQLException{
        String sql = "update bankuser set userbalance=userbalance-100 where userid = 1";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.executeUpdate();
    }
    public static void recieve(Connection conn) throws SQLException{
        String sql = "update bankuser set userbalance=userbalance+100 where userid = 2";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.executeUpdate();
    }
}

 

四、数据库连接需要的数据

oracle.jdbc.driver.OracleDriver
"jdbc:oracle:thin:@//localhost:1521/ORCL"    "scott"    "123456"

"com.mysql.jdbc.Driver"
"jdbc:mysql://localhost:3306/RUNOOB"   "root"    "123456"