JDBC连接MySQL57

JDBC主要功能：

• 建立与数据库或者其他数据源的链接；
• 向数据库发送SQL命令；
• 处理数据库的返回结果；

建表：

public void create(){
        Connection connection=null;
        PreparedStatement ppst=null;
        try{
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //连接数据库
            connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&characterEncoding=utf-8",
                    "root","root");
            String sql="create table user1(id int(10) not null AUTO_INCREMENT primary key , "
                    +"name varchar(30) not null ,"
                    +"password varchar(30) not null )";
            ppst=connection.prepareStatement(sql);
            ppst.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(ppst!=null){
                try {
                    ppst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

增加数据：

public void insert(){
        Connection connection=null;
        PreparedStatement ppst=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&chatacterEncoding=utf-8",
                    "root","root");
            String sql="insert into user1(name,password) values (?,?)";
            ppst=connection.prepareStatement(sql);
            ppst.setString(1,"iverson");
            ppst.setString(2,"asd");
            ppst.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(ppst!=null){
                try {
                    ppst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

删除数据：

public void del(){
        Connection connection=null;
        PreparedStatement ppst=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&characterEncoding=utf-8",
                    "root","root");
            String sql="delete from user1 where id=?";
            ppst=connection.prepareStatement(sql);
            ppst.setInt(1,2);
            ppst.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(ppst!=null){
                try {
                    ppst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

查询：

 

public void queryAll(){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try{
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //连接数据库
            connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&characterEncoding=utf-8",
                    "root","root");
            String sql="select * from user";
            preparedStatement=connection.prepareStatement(sql);
            resultSet=preparedStatement.executeQuery();
            while(resultSet.next()){
                System.out.println(resultSet.getString("username")
                +":"+resultSet.getString("password"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(resultSet!=null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(preparedStatement!=null){
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

public void queryByCondition(){
        Connection connection=null;
        PreparedStatement ppst=null;
        ResultSet rst=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&characterEncoding=utf-8",
                    "root","root");
            String sql="select name from user1 where id=?";
            ppst=connection.prepareStatement(sql);
            ppst.setInt(1,1);
            rst=ppst.executeQuery();
            while (rst.next()){
                System.out.println(rst.getString("name"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(rst!=null){
                try {
                    rst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ppst!=null){
                try {
                    ppst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

修改数据：

public void update(){
        Connection connection=null;
        PreparedStatement ppst=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&characterEncoding=utf-8",
                    "root","root");
            String sql="update user1 set name=? where id=?";
            ppst=connection.prepareStatement(sql);
            ppst.setString(1,"iverson");
            ppst.setInt(2,1);
            ppst.execute();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(ppst!=null){
                try {
                    ppst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

过程调用：

drop procedure if exists P_1;
delimiter $
create procedure P_1()
  BEGIN
      SELECT AVG(products.prod_price) as num
      from products;
  end $
call P_1();

public void testProduce(){
        Connection connection=null;
        CallableStatement cs=null;
        ResultSet rst=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&characterEncoding=utf-8",
                    "root","root");
            String sql="call p_1()";
            cs=connection.prepareCall(sql);
            boolean flag=cs.execute();
            while (flag){
                rst=cs.getResultSet();
                while(rst.next()){
                    System.out.println(rst.getString("num"));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(rst!=null){
                try {
                    rst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(cs!=null){
                try {
                    cs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

函数调用：

drop function if exists f_1;
delimiter $
create function f_1(
  in_vend_id int,
  in_what float
)returns int
  BEGIN
    SET @temp:=0;
    SELECT sum(prod_price*in_what) INTO @temp FROM products WHERE vend_id=in_vend_id;
    return @temp;
  end $

select f_1(1001,1)

public void testFun(){
        Connection connection=null;
        CallableStatement cs=null;
        ResultSet rst=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            connection=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bz?useUnicode=true&characterEncoding=utf-8",
                    "root","root");
            String sql="{?=call f_1(?,?)}";
            cs=connection.prepareCall(sql);
            cs.setString(2,"1001");
            cs.setFloat(3,0.1f);
            cs.registerOutParameter(1,Type.FLOAT);
            cs.execute();
            System.out.println(cs.getObject(1));
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(rst!=null){
                try {
                    rst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(cs!=null){
                try {
                    cs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection!=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }