JDBC相关知识学习

原创 于 2019-08-29 21:15:28 发布 · 287 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

JDBC相关知识学习

JDBC : java数据库连接
JDBC:一套通用的java语言操作不同数据库的接口；

java连接数据库：

//加载驱动
 Class.forName("com.mysql.jdbc.Driver");
//获取数据库名称，登录名，密码
      String url="jdbc:mysql://localhost:3306/lala";
      String username="root";
      String password="123456";
//创建连接      
        Connection connection = DriverManager.getConnection(url, username, password);
//获取操作对象
        Statement statement = connection.createStatement();
//书写sql语句        
        String sql="insert into 表一(name,decl) values('战三','学生')";
//执行sql语句        
        statement.executeUpdate(sql);
//释放资源        
        connection.close();
        statement.close();

DriverManager 驱动管理类

Connection 接口，与数据库建立连接

DML：查询表中数据 executeUpdate()

DQL： 增删改 executeQuery()

查询表中数据

ResultSet: 结果集对象，查询结果封装在结果集对象中



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

        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/lala", "root", "123456");

        Statement statement = con.createStatement();

        String sql="select * from 表一";
        ResultSet resultSet = statement.executeQuery(sql);
        ArrayList<User> list = new ArrayList<>();

        while(resultSet.next()){
            int anInt = resultSet.getInt(1);
            String username = resultSet.getString(2);
            String decl = resultSet.getString(3);
            User user = new User(username, decl);
            list.add(user);

        }
        Iterator<User> iterator = list.iterator();
        while(iterator.hasNext()){
            User next = iterator.next();
            String username = next.getUsername();
            String decl = next.getDecl();
            System.out.println(username+"==="+decl);
        }
        con.close();
        statement.close();
        resultSet.close();
查询语句：加入变量名

登录案例：

变量一：name 变量二： password

String sql =“ select * from 表名 where 列名= ‘ “+变量名1+” ’ and 列名=‘ ”+变量名+ “ ’ ”；

  Scanner scanner = new Scanner(System.in);
        System.out.println("请输入姓名：");
        String username = scanner.nextLine();
        System.out.println("请输入特征描述：");
        String decl = scanner.nextLine();
        Class.forName("com.mysql.jdbc.Driver");
        //jdbc:mysql://localhost:3306/lala
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/lala","root","123456");
        PreparedStatement statement = connection.prepareStatement("select * from 表一 where name=?and decl=?");
        statement.setString(1,username);
        statement.setString(2,decl);
        ResultSet resultSet = statement.executeQuery();
       if(resultSet.next()) {
           System.out.println("登陆成功");}else{
           System.out.println("登陆失败！！");
       }
查询语句：预编译操作对象

prepareStatement=prepareStatement(“ select * from 表名 where 列名= ? and 列名=? ”)

注：此对象需要提前给出sql语句

参数使用?占位

给问号赋值：

prepareStatement.set参数类型（第几位问号，变量名）

ctrl alt +f 快速抽取成员变量

测试类
public class test {
    public static void main(String[] args) throws Exception {

        Connection connection = Util.getconnection();

        PreparedStatement statement = connection.prepareStatement("select * from 表一");
        ResultSet resultSet = statement.executeQuery();
        while(resultSet.next()){
            int anInt = resultSet.getInt(1);
            String username = resultSet.getString(2);
            String decl = resultSet.getString(3);
            User user = new User(username, decl);
            System.out.println(anInt+"==="+user);
        }
        Util.close(connection,statement,resultSet);
    }
}

连接类
public class Util {
    private static  String url;
    private static  String username;
    private static  String password;
    public Util() {

    }
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileReader("proterise.ptoterise"));

            Class.forName("com.mysql.jdbc.Driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getconnection() throws Exception {
        Connection connection = DriverManager.getConnection(url,username,password);
        return connection;
    }
    public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) throws Exception {
        if (connection != null) {
            connection.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (resultSet != null) {
            resultSet.close();
        }

    }
    public static void close(Connection connection, PreparedStatement statement) throws Exception {
        if (connection != null) {
            connection.close();
        }
        if (statement != null) {
            statement.close();
        }

    }
}

批量插入数据库中
调用： addBatch()//添加批处理
executeBatch() //执行批处理
clearBatch() //清空批处理

批处理：
工具类：
public class Util {
    private static  String url;
    private static  String username;
    private static  String password;
    public Util() {

    }
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileReader("proterise.ptoterise"));

            Class.forName("com.mysql.jdbc.Driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getconnection() throws Exception {
        Connection connection = DriverManager.getConnection(url,username,password);
        return connection;
    }
    public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) throws Exception {
        if (connection != null) {
            connection.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (resultSet != null) {
            resultSet.close();
        }

    }
    public static void close(Connection connection, PreparedStatement statement) throws Exception {
        if (connection != null) {
            connection.close();
        }
        if (statement != null) {
            statement.close();
        }

    }
}
用户信息类
public class User {
    private  int id;
    private  String username;
    private String decl;

    public int getId() {
        return id;
    }

    @Override
    public String toString() {
        return "User{" +
                "username='" + username + '\'' +
                ", decl='" + decl + '\'' +
                '}';
    }

    public User(int id,String username, String decl) {
        this.id=id;
        this.username = username;
        this.decl = decl;
    }

    public String getUsername() {
        return username;
    }

    public String getDecl() {
        return decl;
    }
}
测试类
public class test2 {
    public static void main(String[] args) throws Exception {
        //批处理
        Connection getconnection = Util.getconnection();
        ArrayList<User> users = new ArrayList<>();
        for (int i = 0; i < 10000; i++) {
            User lala = new User(i, "lala", "123456");
            users.add(lala);
        }
            String sql="insert into 表一  values(?,?,?)";
        PreparedStatement preparedStatement = getconnection.prepareStatement(sql);
        for (User user : users) {
            preparedStatement.setInt(1,user.getId());
            preparedStatement.setString(2,user.getUsername());
            preparedStatement.setString(3,user.getDecl());
            preparedStatement.addBatch();//添加批处理
        }
        preparedStatement.executeBatch();//执行批处理
        preparedStatement.clearBatch();//清空批处理
        Util.close(getconnection,preparedStatement);
    }
}
Java代码调用存储过程
1.获取能够调用存储过程，或者自定义函数的操作对象
prepareCall(sql);
2.sql语句书写
sql="{call <存储过程名称>[(参数一),(参数二),...]}";
一般参数用?占位；
输入参数赋值：
set参数类型(第几个问号,参数值)
输出参数：需要注册输出参数：
registerOutParameter(第几个问号,Types.参数类型)
3.调用execute() 执行
4.使用get方法获取结果

调用函数： select 函数名（参数一，参数二）；

java调用自定义，内置方法：
1.获取能够调用存储过程，或者自定义函数的操作对象
prepareCall(sql);
2.sql语句书写
sql="{?=call <自定义方法名称>[(参数一),(参数二),...]}";
一般参数用?占位；
输入参数赋值：
set参数类型(第几个问号,参数值)
输出参数：需要注册输出参数：
registerOutParameter(第几个问号,Types.参数类型)
3.调用execute() 执行
4.使用get方法获取结果
获取自增长键值
1.首先在预编译声明中增加Statement.RETURN_GENERATED_KEYS参数变量，表示返回自增长键的值
prepareStatement(sql，Statement.RETURN_GENERATED_KEYS);
2.获取自增长值的结果值
getGeneratedKeys()//获取自增长键

案例：商品的买入，与订单的删除

//登录类
public class denglu {
     public static int anInt;
    public static boolean Dl(Connection connection) throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入你的用户名：");
        String name = scanner.nextLine();
        System.out.println("请输入密码：");
        String pass_word = scanner.nextLine();
        String pass = md5.MD5(connection, pass_word);
        PreparedStatement statement = connection.prepareStatement("select * from user where User_name=?and Pass_word=?");
        statement.setString(1,name);
        statement.setString(2,pass);
        ResultSet resultSet = statement.executeQuery();

        if(resultSet.next()){
            anInt = resultSet.getInt(1);
            return true;
        }else{
            return false;
        }
    }
//获取商品信息线程
public class infor extends Thread {
    public Connection con;

    public infor(Connection connection) {
        this.con=connection;
    }
    @Override
    public void run() {
        try {
            PreparedStatement statement = con.prepareStatement("select * from shopping");
            ResultSet resultSet = statement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt(1); //获取商品编号
                String name = resultSet.getString(2); //商品名称
                int price = resultSet.getInt(3); //获取商品价格
                int num= resultSet.getInt(4); //获取商品数量
                if(num>0){
                    System.out.println("商品编号："+id+"\t"+"商品名称："+name+"\t"+"商品价格："+price+"\t");
                }else{
                    System.out.println("商品名称："+name+"  货物卖完。"+"\t");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
//注册类
public class Login {
    public Login() {
    }
    public static boolean zhuce( Connection connection) throws Exception {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入你的姓名：");
        String name = scanner.nextLine();
        System.out.println("请输入密码：");
        String pass_word = scanner.nextLine();

        String pass = md5.MD5(connection, pass_word);
        PreparedStatement statement = connection.prepareStatement("insert into user(User_name,Pass_word) values(?,?)");
        statement.setString(1,name);
        statement.setString(2,pass);
        int i = statement.executeUpdate();
        if(i>0){
           return true;
        }else{
           return false;
        }
    }
}
//密码加密类
public class md5 {
    public md5() {
    }
    public static String MD5(Connection connection, String pass_word) throws Exception {

        //sql="{?=call <自定义方法名称>[(参数一),(参数二),...]}";
        //调用内置函数，进行密码加密；
        String sql="{?=call md5(?)}";
        CallableStatement call = connection.prepareCall(sql);
        call.registerOutParameter(1, Types.VARCHAR);
        call.setString(2,pass_word);
        call.execute();
        String string = call.getString(1);
        call.close();
        return string;
    }
}
//菜单类
public class menu {
    private static Connection con;
   public static  void menu1(Connection connection) throws Exception {
      con=connection;
       System.out.println("菜单项: 1.下订单 2.查看订单 3.删除订单 4.退出");
       Scanner scanner = new Scanner(System.in);
       int i = scanner.nextInt();
       switch(i){
           case 1:
               add();
               break;
           case 2:
               look();
               break;
           case 3:
               dele();
               break;
           case 4:
               con.close();
              System.exit(0);
       }
       con.close();
   }

    private static void dele() throws SQLException {
        PreparedStatement statement = con.prepareStatement("delete  from connecting where connecting.`User_id`=?");
        statement.setInt(1,denglu.anInt);
        int i = statement.executeUpdate();
        if(i>0){
            System.out.println("删除成功！");
        }else{
            System.out.println("删除失败！");
        }

    }

    private static void look() throws SQLException {
        PreparedStatement statement = con.prepareStatement("select shopping.*,connecting.`count` from connecting,shopping where connecting.`Shopping_id`=shopping.`Shopping_id` and User_id=?");
        statement.setInt(1,denglu.anInt);
        ResultSet resultSet = statement.executeQuery();
        while(resultSet.next()){
            int id = resultSet.getInt(1); //获取商品编号
            String name = resultSet.getString(2); //商品名称
            int price = resultSet.getInt(3); //获取商品价格
            int num= resultSet.getInt(4); //获取商品数量
            int zs = resultSet.getInt(5);
            int zongjia= zs*price;
            System.out.println("商品编号："+id+"\t"+"商品名称："+name+"\t"+"商品价格："+price+"\t"+"购买数量："+zs+"\t"+"总价："+zongjia);

        }
    }
//测试类
public class test {
    public static void main(String[] args) throws Exception {
        System.out.println("请选择: 1.注册   2.登录");
        Connection connection = Util.getconnection();
        Scanner scanner = new Scanner(System.in);
        int i = scanner.nextInt();
        switch(i){
            case 1:
                boolean zhuce = Login.zhuce(connection);
                if(!zhuce){
                  break;
                }

            case 2:
                System.out.println("请登录：");
                boolean dl = denglu.Dl(connection);
                if(dl){
                System.out.println("登录成功");
                menu.menu1(connection);
            }else{
                System.out.println("登录失败");
            }
            break;
        }
    }
}
//连接数据库工具类
public class Util {
    private static  String url;
    private static  String username;
    private static  String password;
    public Util() {

    }
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileReader("proterise.ptoterise"));

            Class.forName("com.mysql.jdbc.Driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getconnection() throws Exception {
        Connection connection = DriverManager.getConnection(url,username,password);
        return connection;
    }
    public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) throws Exception {
        if (connection != null) {
            connection.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (resultSet != null) {
            resultSet.close();
        }

    }
    public static void close(Connection connection, PreparedStatement statement) throws Exception {
        if (connection != null) {
            connection.close();
        }
        if (statement != null) {
            statement.close();
        }
    }
}
Amber_Flying
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值