认识达内从这里开始

认真做教育专心促就业

Java——JDBC基础

发布：济南IT培训
来源：互联网
时间：2018-04-17 17:01

    1,连接数据库-济南IT培训整理
    2,连接数据库创建表userinfo
    3,连接数据库创建表序列seq_userinfo_id
    4,连接数据库关联查询员工信息输出
    ResultSet rs = state.executeQuery(sql);
    while(rs.next()){}
    5,连接数据库执行DML操作---插入操作插入数据
    插入语句的时候使用序列seq_userinfo_id.NEXTVAL
    6,连接数据库实现注册功能---就是插入新用户
    学会SQL语句中用+连接
    7,连接数据库查看员工信息和分页查看
    分页查询的时候要注意两个ROWNUM
    start和end
    8,创建工具类DBUtil,并测试该工具类
    ////////////实验代码
    package part1;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;
    public class JDBCDemo1 {
    public static void main(String[] args) {
    try {
    //1,加载驱动
    Class.forName("oracle.jdbc.driver.OracleDriver");
    //2,通过DriverManager根据驱动建立连接(Connection)
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system", "orcl123");
    System.out.println("连接数据库成功...");
    //3,通过Connection创建用于执行SQL的Statement
    Statement state = conn.createStatement();
    //4,通过Statement执行SQL语句
    /*
    * 4
    * Statement根据执行的SQL语句的不同
    * 提供了相应的几个执行方法
    *
    * boolean execute(String sql)
    * 通常用来执行DDL语句,但实际上任何语句都可以用该方法执行
    *
    * int executeUpdate(String sql)
    * 专门用来执行DML语句,返回值为执行该SQL后影响数据库表中多少条数据
    *
    * ResultSet executeQuery(String sql)
    * 专门用来执行DQL语句,返回值为查询的结果集
    * */
    String sql = "SELECT ename,job,sal,deptno FROM emp";
    //执行查询语句
    ResultSet rs = state.executeQuery(sql);
    /*
    * 遍历结果集
    * ResultSet提供了方法:
    * boolean next()
    * 该方法会使当前结果集向下表示一条记录,
    * 若结果集还有下一条记录,就表示它并返回true,
    * 若结果集已经没有记录了,则返回false.
    * 在遍历结果集时,至少先执行一次next方法,
    * 因为及结果集指针默认在第一条记录之上.
    * */
    while(rs.next()){
    String ename = rs.getString("ename");
    String job = rs.getString("job");
    int sal = rs.getInt("sal");
    int deptno = rs.getInt("deptno");
    System.out.println("ename="+ename+",job="+job+",sal="+sal+",deptno="+deptno);
    }
    conn.close();
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
    /////////////////////////////////////////////////
    package part1;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;
    public class JDBCDemo2 {
    public static void main(String[] args) {
    try {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "system", "orcl123");
    System.out.println("连接数据库成功...");
    Statement state = conn.createStatement();
    String sql = "CREATE TABLE mytable("
    + "id NUMBER(5),"
    + "username VARCHAR2(40),"
    + "password VARCHAR2(40)"
    + ")";
    System.out.println(sql);
    state.execute(sql);
    System.out.println("创建表完毕...");
    conn.close();
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
    //////////////////////////////////////////////////
    package part1;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;
    /**
    *
    * @description 创建序列
    * @createTime 2017年10月25日下午2:12:47
    */
    public class JDBCDemo3 {
    public static void main(String[] args) {
    try {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(
    "jdbc:orcal:thin:@localhost:1521:orcl", "system", "orcl123");
    System.out.println("连接数据库成功...");
    Statement state = conn.createStatement();
    String sql = "CREATE SEQUENCE seq_mytable_id "
    + "START WITH 1 "
    + "INCREMENT BY 1";
    System.out.println(sql);
    state.execute(sql);
    System.out.println("序列创建成功...");
    conn.close();
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
    ////////////////////////////////////
    package part1;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;
    public class JDBCDemo4 {
    public static void main(String[] args) {
    try {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(
    "jdbc:oracle:thin:@localhost:1521:orcl", "system", "orcl123");
    System.out.println("数据库连接成功...");
    Statement state = conn.createStatement();
    /*
    SELECT e.ename,e.job,e.sal,e.deptno,d.dname,d.loc
    FROM emp e, dept d
    where e.deptno = d.deptno;
    */
    String sql = "SELECT e.ename,e.job,e.sal,e.deptno,d.dname,d.loc "
    + "FROM emp e, dept d "
    + "WHERE e.deptno = d.deptno";
    System.out.println(sql);
    ResultSet rs = state.executeQuery(sql);
    while(rs.next()){
    String ename = rs.getString("ename");
    String job = rs.getString("job");
    int sal = rs.getInt("sal");
    int deptno = rs.getInt("deptno");
    String dname = rs.getString("dname");
    String loc = rs.getString("loc");
    System.out.println("ename="+ename+",job="+job+",sal="+sal+",deptno="+deptno+",dname="+"dname"+",loc="+loc);
    }
    conn.close();
    System.out.println("connection closed");
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
    ///////////////////////////////////////////
    package part1;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;
    /**
    *

* @description 向userinfo表中插入数据

济南IT培训

    * @createTime 2017年10月25日下午3:42:52
    */
    public class JDBCDemo5 {
    public static void main(String[] args) {
    try {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(
    "jdbc:oracle:thin:@localhost:1521:orcl", "system", "orcl123");
    System.out.println("连接数据库成功...");
    Statement state = conn.createStatement();
    String sql = "INSERT INTO userinfo "
    +"(id,username,password,account,email) "
    +"VALUES "
    +"(seq_userinfo_id.NEXTVAL,'dajiba','123456',5000,'dajiba@tedu.cn')";
    System.out.println(sql);
    int num = state.executeUpdate(sql);
    if(num>0){
    System.out.println("插入成功");
    }else{
    System.out.println("插入失败");
    }
    conn.close();
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
    //////////////////////////////////////
    package part1;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;
    import java.util.Scanner;
    /**
    *
    * @description 模拟实现简单的注册功能
    * @createTime 2017年10月25日下午3:52:56
    */
    public class JDBCDemo6 {
    public static void main(String[] args) {
    System.out.println("------模拟注册功能------");
    //连接数据库
    try {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(
    "jdbc:oracle:thin:@localhost:1521:orcl", "system", "orcl123");
    System.out.println("连接数据库成功...");
    Scanner scanner = new Scanner(System.in);
    System.out.println("请输入用户名称:");
    String username = scanner.nextLine();
    System.out.println("请输入用户密码:");
    String password = scanner.nextLine();
    System.out.println("请输入用户邮箱:");
    String mail = scanner.nextLine();
    Statement state = conn.createStatement();
    String sql = "INSERT INTO userinfo "
    +"(id,username,password,account,email) "
    +"VALUES "
    +"(seq_userinfo_id.NEXTVAL,'"
    +username+"','"+password+"',15000,'"+mail+"')";
    System.out.println(sql);
    int num = state.executeUpdate(sql);
    if(num > 0){
    System.out.println("恭喜,注册成功!");
    }else{
    System.out.println("遗憾,注册失败!");
    }
    conn.close();
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
    //////////////////////////////////
    package part1;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.Statement;
    import java.util.Scanner;
    /**
    * select *
    from(
    select ROWNUM rn,t.*
    from(
    select *
    from emp
    order by sal asc) t
    where ROWNUM<=5)
    where rn>2
    * @description 模拟分页查询功能
    * @createTime 2017年10月25日下午4:14:39
    */
    public class JDBCDemo7 {
    public static void main(String[] args) {
    try {
    System.out.println("模拟分页查询功能");
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(
    "jdbc:oracl:thin:@localhost:1521:orcl", "system", "orcl123");
    System.out.println("连接数据库成功>>>>>");
    Scanner scanner = new Scanner(System.in);
    System.out.println("希望每页查看几条数据:");
    int pageSize = Integer.parseInt(scanner.nextLine());
    System.out.println("希望查看第几页:");
    int page = Integer.parseInt(scanner.nextLine());
    int start = (page-1)*pageSize+1;
    int end = page*pageSize;
    Statement state = conn.createStatement();
    String sql = "select * "
    + "from( "
    + "select ROWNUM rn,t.* "
    + "from( "
    + "select * "
    + "from emp "
    + "order by sal asc) t "
    + "where ROWNUM<="+end+") "
    + "where rn>="+start;
    ResultSet rs = state.executeQuery(sql);
    while(rs.next()){
    int empno = rs.getInt("empno");
    String ename = rs.getString("ename");
    int sal = rs.getInt("sal");
    String job = rs.getString("job");
    int deptno = rs.getInt("deptno");
    System.out.println(empno+","+ename+","+sal+","+job+","+deptno);
    }
    conn.close();
    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    }
    //////////////////////////////////
    package part1;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.Statement;
    public class JDBCDemo8 {
    public static void main(String[] args) {
    try {
    Connection conn = DBUtil.getConnection();
    System.out.println("连接数据库成功...");
    Statement state = conn.createStatement();
    String sql = "SELECT ename,job,sal,deptno FROM emp";
    ResultSet rs = state.executeQuery(sql);
    while(rs.next()){
    String ename = rs.getString("ename");
    String job = rs.getString("job");
    int sal = rs.getInt("sal");
    int deptno = rs.getInt("deptno");
    System.out.println("ename="+ename+",job="+job+",sal="+sal+",deptno="+deptno);
    }
    //conn.close();
    } catch (Exception e) {
    e.printStackTrace();
    } finally {
    DBUtil.closeConnection();
    }
    }
    }
    ///////////////////////////////////////
    package part1;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.Properties;
    import org.apache.commons.dbcp.BasicDataSource;
    /**
    *
    * @description 工具类,负责管理数据库连接
    * @createTime 2017年10月25日下午4:38:21
    */
    public class DBUtil {
    /*
    * ThreadLocal内部是一个map
    * key是线程
    */
    private static ThreadLocal<Connection> tl;
    /*
    * 线程池
    */
    private static BasicDataSource ds;
    /*
    * 静态块初始化
    */
    static{
    tl = new ThreadLocal<Connection>();
    //加载配置文件
    Properties prop = new Properties();
    try {
    prop.load(new FileInputStream("config.properties"));
    String driver = prop.getProperty("driver");
    String url = prop.getProperty("url");
    String username = prop.getProperty("username");
    String password = prop.getProperty("password");
    //连接池最大连接数
    int maxActive = Integer.parseInt(prop.getProperty("maxactive"));
    //当没有可用连接时最大等待时间
    int maxWait = Integer.parseInt(prop.getProperty("maxwait"));
    //打桩
    //System.out.println(driver);
    //System.out.println(url);
    //System.out.println(username);
    //System.out.println(password);
    //System.out.println(maxActive);
    //System.out.println(maxWait);
    //初始化连接池
    ds = new BasicDataSource();
    ds.setDriverClassName(driver);
    ds.setUrl(url);
    ds.setUsername(username);
    ds.setPassword(password);
    ds.setMaxActive(maxActive);
    ds.setMaxWait(maxWait);
    } catch (FileNotFoundException e) {
    e.printStackTrace();
    } catch (IOException e) {
    e.printStackTrace();
    }
    }
    public static Connection getConnection() throws SQLException{
    Connection conn = ds.getConnection();
    tl.set(conn);
    System.out.println("连接数据库成功");
    return conn;
    }
    public static void closeConnection(){
    Connection conn = tl.get();
    if(null != conn){
    try {
    conn.close();
    tl.remove();
    System.out.println("数据库连接已关闭...");
    } catch (SQLException e) {
    e.printStackTrace();
    }
    }
    }
    }
    ///////////////////////////////////////
    配置文件代码
    driver=oracle.jdbc.driver.OracleDriver
    url=jdbc:oracle:thin:@localhost:1521:orcl
    username=system
    password=orcl123
    maxactive=1

maxwait=5000

更多济南IT培训相关咨询,请扫描下方二维码

济南IT培训