jdbc学习

 jdbc入门

2.1 之前操作数据

  1)通过mysql的客户端工具,登录数据库服务器  (mysql -u root -p 密码)

  2)编写sql语句

  3)发送sql语句到数据库服务器执行

 

2.2 什么是jdbc

 使用java代码(程序)发送sql语句的技术,就是jdbc技术!!!!

2.3 使用jdbc发送sql前提

登录数据库服务器(连接数据库服务器)

数据库的IP地址

端口

数据库用户名

密码

 

/**

 * jdbc连接数据库

 * @author APPle

 *

 */

public class Demo1 {

//连接数据库的URL

private String url = "jdbc:mysql://localhost:3306/day17";

                    // jdbc协议:数据库子协议:主机:端口/连接的数据库   //

 

private String user = "root";//用户名

private String password = "root";//密码

 

/**

 * 第一种方法

 * @throws Exception

 */

@Test

public void test1() throws Exception{

//1.创建驱动程序类对象

Driver driver = new com.mysql.jdbc.Driver(); //新版本

//Driver driver = new org.gjt.mm.mysql.Driver(); //旧版本

 

//设置用户名和密码

Properties props = new Properties();

props.setProperty("user", user);

props.setProperty("password", password);

 

//2.连接数据库,返回连接对象

Connection conn = driver.connect(url, props);

 

System.out.println(conn);

}

 

/**

 * 使用驱动管理器类连接数据库(注册了两次,没必要)

 * @throws Exception

 */

@Test

public void test2() throws Exception{

Driver driver = new com.mysql.jdbc.Driver();

//Driver driver2 = new com.oracle.jdbc.Driver();

//1.注册驱动程序(可以注册多个驱动程序)

DriverManager.registerDriver(driver);

//DriverManager.registerDriver(driver2);

 

//2.连接到具体的数据库

Connection conn = DriverManager.getConnection(url, user, password);

System.out.println(conn);

 

}

 

/**

 * (推荐使用这种方式连接数据库)

 * 推荐使用加载驱动程序类  来 注册驱动程序

 * @throws Exception

 */

@Test

public void test3() throws Exception{

//Driver driver = new com.mysql.jdbc.Driver();

 

//通过得到字节码对象的方式加载静态代码块,从而注册驱动程序

Class.forName("com.mysql.jdbc.Driver");

 

//Driver driver2 = new com.oracle.jdbc.Driver();

//1.注册驱动程序(可以注册多个驱动程序)

//DriverManager.registerDriver(driver);

//DriverManager.registerDriver(driver2);

 

//2.连接到具体的数据库

Connection conn = DriverManager.getConnection(url, user, password);

System.out.println(conn);

 

}

 

}

 

2.4 JDBC接口核心的API

java.sql.*   和  javax.sql.*

 

|- Driver接口: 表示java驱动程序接口。所有的具体的数据库厂商要来实现此接口。

|- connect(url, properties):  连接数据库的方法。

url: 连接数据库的URL

URL语法: jdbc协议:数据库子协议://主机:端口/数据库

user: 数据库的用户名

password: 数据库用户密码

|- DriverManager类: 驱动管理器类,用于管理所有注册的驱动程序

|-registerDriver(driver)  : 注册驱动类对象

|-Connection getConnection(url,user,password);  获取连接对象

 

|- Connection接口: 表示java程序和数据库的连接对象。

|- Statement createStatement() 创建Statement对象

|- PreparedStatement prepareStatement(String sql)  创建PreparedStatement对象

|- CallableStatement prepareCall(String sql) 创建CallableStatement对象

 

|- Statement接口: 用于执行静态的sql语句

|- int executeUpdate(String sql)   执行静态的更新sql语句(DDLDML

|- ResultSet executeQuery(String sql)  :执行的静态的查询sql语句(DQL

 

|-PreparedStatement接口:用于执行预编译sql语句

|- int executeUpdate() 执行预编译的更新sql语句(DDLDML

|-ResultSet executeQuery()   执行预编译的查询sql语句(DQL

 

|-CallableStatement接口:用于执行存储过程的sql语句(call xxx

|-ResultSet executeQuery()   调用存储过程的方法

 

 

|- ResultSet接口:用于封装查询出来的数据

|- boolean next()  将光标移动到下一行

|-getXX() : 获取列的值

 

3 使用Statement执行sql语句

3.1 执行DDL语句

    /**

 * 执行DDL语句(创建表)

 */

@Test

public void test1(){

Statement stmt = null;

Connection conn = null;

try {

//1.驱动注册程序

Class.forName("com.mysql.jdbc.Driver");

 

//2.获取连接对象

conn = DriverManager.getConnection(url, user, password);

 

//3.创建Statement

stmt = conn.createStatement();

 

//4.准备sql

String sql = "CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),gender VARCHAR(2))";

 

//5.发送sql语句,执行sql语句,得到返回结果

int count = stmt.executeUpdate(sql);

 

//6.输出

System.out.println("影响了"+count+"行!");

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e);

} finally{

//7.关闭连接(顺序:后打开的先关闭)

if(stmt!=null)

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException(e);

}

if(conn!=null)

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException(e);

}

}

}

 

  3.2 执行DML语句

/**

 * 使用Statement执行DML语句

 * @author APPle

 *

 */

public class Demo2 {

private String url = "jdbc:mysql://localhost:3306/day17";

private String user = "root";

private String password = "root";

 

/**

 * 增加

 */

@Test

public void testInsert(){

Connection conn = null;

Statement stmt = null;

try {

//通过工具类获取连接对象

conn = JdbcUtil.getConnection();

 

//3.创建Statement对象

stmt = conn.createStatement();

 

//4.sql语句

String sql = "INSERT INTO student(NAME,gender) VALUES('李四','')";

 

//5.执行sql

int count = stmt.executeUpdate(sql);

 

System.out.println("影响了"+count+"");

 

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e);

} finally{

//关闭资源

/*if(stmt!=null)

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException(e);

}

if(conn!=null)

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException(e);

}*/

JdbcUtil.close(conn, stmt);

}

}

 

/**

 * 修改

 */

@Test

public void testUpdate(){

Connection conn = null;

Statement stmt = null;

//模拟用户输入

String name = "陈六";

int id = 3;

try {

/*//1.注册驱动

Class.forName("com.mysql.jdbc.Driver");

 

//2.获取连接对象

conn = DriverManager.getConnection(url, user, password);*/

//通过工具类获取连接对象

conn = JdbcUtil.getConnection();

 

//3.创建Statement对象

stmt = conn.createStatement();

 

//4.sql语句

String sql = "UPDATE student SET NAME='"+name+"' WHERE id="+id+"";

 

System.out.println(sql);

 

//5.执行sql

int count = stmt.executeUpdate(sql);

 

System.out.println("影响了"+count+"");

 

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e);

} finally{

//关闭资源

/*if(stmt!=null)

try {

stmt.close();

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException(e);

}

if(conn!=null)

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException(e);

}*/

JdbcUtil.close(conn, stmt);

}

}

 

/**

 * 删除

 */

@Test

public void testDelete(){

Connection conn = null;

Statement stmt = null;

//模拟用户输入

int id = 3;

try {

/*//1.注册驱动

Class.forName("com.mysql.jdbc.Driver");

 

//2.获取连接对象

conn = DriverManager.getConnection(url, user, password);*/

//通过工具类获取连接对象

conn = JdbcUtil.getConnection();

 

//3.创建Statement对象

stmt = conn.createStatement();

 

//4.sql语句

String sql = "DELETE FROM student WHERE id="+id+"";

 

System.out.println(sql);

 

//5.执行sql

int count = stmt.executeUpdate(sql);

 

System.out.println("影响了"+count+"");

 

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e);

} finally{

//关闭资源

/*if(stmt!=null)

try {

stmt.close(); 

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException(e);

}

if(conn!=null)

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

throw new RuntimeException(e);

}*/

JdbcUtil.close(conn, stmt);

}

}

}

 

  3.3 执行DQL语句

/**

 * 使用Statement执行DQL语句(查询操作)

 * @author APPle

 */

public class Demo3 {

 

@Test

public void test1(){

Connection conn = null;

Statement stmt = null;

try{

//获取连接

conn = JdbcUtil.getConnection();

//创建Statement

stmt = conn.createStatement();

//准备sql

String sql = "SELECT * FROM student";

//执行sql

ResultSet rs = stmt.executeQuery(sql);

 

//移动光标

/*boolean flag = rs.next();

 

flag = rs.next();

flag = rs.next();

if(flag){

//取出列值

//索引

int id = rs.getInt(1);

String name = rs.getString(2);

String gender = rs.getString(3);

System.out.println(id+","+name+","+gender);

 

//列名称

int id = rs.getInt("id");

String name = rs.getString("name");

String gender = rs.getString("gender");

System.out.println(id+","+name+","+gender);

}*/

 

//遍历结果

while(rs.next()){

int id = rs.getInt("id");

String name = rs.getString("name");

String gender = rs.getString("gender");

System.out.println(id+","+name+","+gender);

}

 

}catch(Exception e){

e.printStackTrace();

throw new RuntimeException(e);

}finally{

JdbcUtil.close(conn, stmt);

}

}

}

 

4 使用PreparedStatement执行sql语句

 

public class Demo1 {

 

/**

 * 增加

 */

@Test

public void testInsert() {

Connection conn = null;

PreparedStatement stmt = null;

try {

//1.获取连接

conn = JdbcUtil.getConnection();

 

//2.准备预编译的sql

String sql = "INSERT INTO student(NAME,gender) VALUES(?,?)"; //?表示一个参数的占位符

 

//3.执行预编译sql语句(检查语法)

stmt = conn.prepareStatement(sql);

 

//4.设置参数值

/**

 * 参数一: 参数位置  从1开始

 */

stmt.setString(1, "李四");

stmt.setString(2, "");

 

//5.发送参数,执行sql

int count = stmt.executeUpdate();

 

System.out.println("影响了"+count+"");

 

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e);

} finally {

JdbcUtil.close(conn, stmt);

}

}

 

/**

 * 修改

 */

@Test

public void testUpdate() {

Connection conn = null;

PreparedStatement stmt = null;

try {

//1.获取连接

conn = JdbcUtil.getConnection();

 

//2.准备预编译的sql

String sql = "UPDATE student SET NAME=? WHERE id=?"; //?表示一个参数的占位符

 

//3.执行预编译sql语句(检查语法)

stmt = conn.prepareStatement(sql);

 

//4.设置参数值

/**

 * 参数一: 参数位置  从1开始

 */

stmt.setString(1, "王五");

stmt.setInt(2, 9);

 

//5.发送参数,执行sql

int count = stmt.executeUpdate();

 

System.out.println("影响了"+count+"");

 

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e);

} finally {

JdbcUtil.close(conn, stmt);

}

}

 

/**

 * 删除

 */

@Test

public void testDelete() {

Connection conn = null;

PreparedStatement stmt = null;

try {

//1.获取连接

conn = JdbcUtil.getConnection();

 

//2.准备预编译的sql

String sql = "DELETE FROM student WHERE id=?"; //?表示一个参数的占位符

 

//3.执行预编译sql语句(检查语法)

stmt = conn.prepareStatement(sql);

 

//4.设置参数值

/**

 * 参数一: 参数位置  从1开始

 */

stmt.setInt(1, 9);

 

//5.发送参数,执行sql

int count = stmt.executeUpdate();

 

System.out.println("影响了"+count+"");

 

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e);

} finally {

JdbcUtil.close(conn, stmt);

}

}

 

/**

 * 查询

 */

@Test

public void testQuery() {

Connection conn = null;

PreparedStatement stmt = null;

ResultSet rs = null;

try {

//1.获取连接

conn = JdbcUtil.getConnection();

 

//2.准备预编译的sql

String sql = "SELECT * FROM student";

 

//3.预编译

stmt = conn.prepareStatement(sql);

 

//4.执行sql

rs = stmt.executeQuery();

 

//5.遍历rs

while(rs.next()){

int id = rs.getInt("id");

String name = rs.getString("name");

String gender = rs.getString("gender");

System.out.println(id+","+name+","+gender);

}

 

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e);

} finally {

//关闭资源

JdbcUtil.close(conn,stmt,rs);

}

}

}

 

PreparedStatement vs Statment

1)语法不同:PreparedStatement可以使用预编译的sql,而Statment只能使用静态的sql

2)效率不同: PreparedStatement可以使用sql缓存区,效率比Statment

3)安全性不同: PreparedStatement可以有效防止sql注入,而Statment不能防止sql注入。

 

推荐使用PreparedStatement

 

5 CallableStatement执行存储过程

/**

 * 使用CablleStatement调用存储过程

 * @author APPle

 *

 */

public class Demo1 {

 

/**

 * 调用带有输入参数的存储过程

 * CALL pro_findById(4);

 */

@Test

public void test1(){

Connection conn = null;

CallableStatement stmt = null;

ResultSet rs = null;

try {

//获取连接

conn = JdbcUtil.getConnection();

 

//准备sql

String sql = "CALL pro_findById(?)"; //可以执行预编译的sql

 

//预编译

stmt = conn.prepareCall(sql);

 

//设置输入参数

stmt.setInt(1, 6);

 

//发送参数

rs = stmt.executeQuery(); //注意: 所有调用存储过程的sql语句都是使用executeQuery方法执行!!!

 

//遍历结果

while(rs.next()){

int id = rs.getInt("id");

String name = rs.getString("name");

String gender = rs.getString("gender");

System.out.println(id+","+name+","+gender);

}

 

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e);

} finally {

JdbcUtil.close(conn, stmt ,rs);

}

}

 

/**

 * 执行带有输出参数的存储过程

 * CALL pro_findById2(5,@NAME);

 */

@Test

public void test2(){

Connection conn = null;

CallableStatement stmt = null;

ResultSet rs = null;

try {

//获取连接

conn = JdbcUtil.getConnection();

//准备sql

String sql = "CALL pro_findById2(?,?)"; //第一个?是输入参数,第二个?是输出参数

 

//预编译

stmt = conn.prepareCall(sql);

 

//设置输入参数

stmt.setInt(1, 6);

//设置输出参数(注册输出参数)

/**

 * 参数一: 参数位置

 * 参数二: 存储过程中的输出参数的jdbc类型    VARCHAR(20)

 */

stmt.registerOutParameter(2, java.sql.Types.VARCHAR);

 

//发送参数,执行

stmt.executeQuery(); //结果不是返回到结果集中,而是返回到输出参数中

 

//得到输出参数的值

/**

 * 索引值: 预编译sql中的输出参数的位置

 */

String result = stmt.getString(2); //getXX方法专门用于获取存储过程中的输出参数

 

System.out.println(result);

 

} catch (Exception e) {

e.printStackTrace();

throw new RuntimeException(e);

} finally {

JdbcUtil.close(conn, stmt ,rs);

}

}

}

 MySqlUtil代码:

package com.sql.util;


import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;







import java.util.Properties;

import javax.management.RuntimeErrorException;

import com.mysql.jdbc.Driver;

public class MysqlUtil {
    private static String url = null;
    private static String user = null;
    private static String password = null;
    private static String driverClass = null;
    static{
        try {
            Properties properties = new Properties();
        //    FileInputStream fileInputStream = new FileInputStream("./src/db.properties");
            InputStream in = MysqlUtil.class.getResourceAsStream("/db.properties");
            
            properties.load(in);
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            driverClass = properties.getProperty("driverClass");
            
            Class.forName(driverClass);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public static Connection getConn() {
        // TODO Auto-generated method stub
        try {
            Connection conn = DriverManager.getConnection(url, user, password);
            return conn;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException();
        }
    }
    public static void close(Connection conn, Statement state) {
        
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(state != null){
            try {
                state.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    public static void close(Connection conn, Statement state, ResultSet resultSet) {
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                throw new RuntimeException();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(state != null){
            try {
                state.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}
View Code

链接数据库练习:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.DriverPropertyInfo;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Statement;
import java.util.Properties;
import java.util.logging.Logger;

import org.junit.Test;

import com.mysql.jdbc.Driver;
import com.sql.util.MysqlUtil;


public class Connect {
    
    private String url = "jdbc:mysql://localhost:3306/handsomecui";
    private String user = "root";
    private String password = "123456";
    
    
    public void test1() throws SQLException {
        Driver driver = new Driver();
            
            
        Properties info = new Properties();
        info.setProperty("user", user);
        info.setProperty("password", password);
        Connection connect = driver.connect(url, info);
        System.out.println(connect);
    }
    
    
    public void test2() throws Exception{
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    }
    
    public void test3() {
        Statement stat = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, password);
            stat = conn.createStatement();
            String sql = "CREATE TABLE mystudent(id INT,NAME VARCHAR(20))";
            int count = stat.executeUpdate(sql);
            System.out.println(count);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            if(stat != null){
                try {
                    stat.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    throw new RuntimeException();
                }
            }
            if(conn != null){
                try {
                    stat.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    throw new RuntimeException();
                }
            }
        }
        
        
    }
    
    @Test
    public void add() {
        Connection conn = null;
        Statement statement = null;
        
        try {
            conn = MysqlUtil.getConn();
            statement = conn.createStatement();
            String sql = "SELECT * FROM mystudent;";
            int count = statement.executeUpdate(sql);
            System.out.println(count);
        } catch (Exception e) {
            // TODO: handle exception
        }finally{
            MysqlUtil.close(conn, statement);
        }
        
        
        
        
    }
}
View Code

查询语句简单练习:

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

import org.junit.Test;





import com.sql.util.MysqlUtil;


public class Query {
    
    
    public void test1() {
        Connection conn = null;
        PreparedStatement state = null;
        try {
            conn = MysqlUtil.getConn();
            String sql = "insert into mystudent values(?,?)";
            
            state = conn.prepareStatement(sql);
            state.setInt(1, 3);
            state.setString(2, "王五");
            int count = state.executeUpdate();
            System.out.println("影响了" + count + "行");
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException();
        }finally{
            MysqlUtil.close(conn, state);
        }
    }
    
    public void test2() {
        // TODO Auto-generated method stub
        Connection conn = null;
        Statement state = null;
        ResultSet result = null;
        try {
            conn = MysqlUtil.getConn();
            
            String sql = "SELECT * FROM mystudent;";
            result = state.executeQuery(sql);
            while(result.next()){
                System.out.println(result.getInt("id") + "," + result.getString("name"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException();
        }finally{
            MysqlUtil.close(conn, state, result);
        }
    }
    
    
    public void test3() {
        // TODO Auto-generated method stub
        Connection conn = null;
        CallableStatement state = null;
        ResultSet result = null;
        try {
            conn = MysqlUtil.getConn();
            String sql = "CALL  pro_query(?)";
            state = conn.prepareCall(sql);
            
            state.setInt(1, 2);
            result = state.executeQuery();
            while(result.next()){
                System.out.println(result.getInt("id") + "," + result.getString("name"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException();
        }finally{
            MysqlUtil.close(conn, state, result);
        }
    }
    
    @Test
    public void test4() {
        Connection conn = null;
        CallableStatement state = null;
        ResultSet resultSet = null;
        try {
            conn = MysqlUtil.getConn();
            String sql = "CALL findNameByid(?,?);";
            state = conn.prepareCall(sql);
            state.setInt(1, 2);
            state.registerOutParameter(2, java.sql.Types.VARCHAR);
            state.executeQuery();
            String result = state.getString(2);
            System.out.println(result);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally{
            MysqlUtil.close(conn, state);
        }
    }
    
}
View Code

 

posted @ 2016-12-22 14:45  handsomecui  阅读(171)  评论(0编辑  收藏  举报