jdbc的学习

jdbc快速入门

package com.yeluo.jdbc;

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

public class jdbc01 {
    public static void main(String[] args) throws Exception {
     //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        String url="jdbc:mysql://127.0.01:3306/demo01";
        String usename="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, usename, password);
        //3.定义sql
        String sql="update tb_s set money=2000 where id=1";
        //4.获取执行sql语句的对象
        Statement stmt = conn.createStatement();

       //5.执行sql
        int i = stmt.executeUpdate(sql);
        //6.处理结果
        System.out.println(i);
        //7.关闭连接
        stmt.close();
        conn.close();
    }
}

jdbc API详解

分类

DriverManger

Connection

• 获取执行sql的对象

• 管理事务

• 

  

Statement

ResultSet

package com.yeluo.jdbc;

import com.yeluo.jdbc.pojo.Account;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class jdbc02_Statement {
    public static void main(String[] args) throws Exception {

     //1.注册驱动
        //Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        String url="jdbc:mysql://127.0.01:3306/demo01?useSSL=false";
        String usename="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, usename, password);
        //3.定义sql
        String sql="select *from tb_s";
        //4.获取执行sql语句的对象
        Statement stmt = conn.createStatement();
            //5.执行sql
        ResultSet rs = stmt.executeQuery(sql);
        //6.处理结果
        List<Account> list=new ArrayList<>();
        while (rs.next()){
            //创建account 对象
            Account account=new Account();
            int id=rs.getInt("id");
            String S_name=rs.getString("S_name");
            double money=rs.getDouble("money");
            //赋值
            account.setId(id);
            account.setMoney(money);
            account.setName(S_name);

            //存入数据
            list.add(account);
        }
        System.out.println(list);
        //7.关闭连接
        rs.close();
        stmt.close();
        conn.close();
    }
}

PreparedStatement

package com.yeluo.jdbc;

import com.yeluo.jdbc.pojo.Account;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class jdbc03_UserLogin {
    public static void main(String[] args) throws Exception {

        //2.获取连接
        String url="jdbc:mysql://127.0.01:3306/demo01?useSSL=false";
        String usename="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, usename, password);


        //接收用户输入用户名，密码
        String name="";
        String pwd="";
        //创建sql 语句
         String sql="select *from tb_s where S_name=? and money=?";
         //获取执行sql语句的对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //设置？的值
          pstmt.setString(1,name);
          pstmt.setString(2,pwd);
          //执行
        ResultSet rs = pstmt.executeQuery();
        //判断登录是否成
            if(rs.next()){
                System.out.println("登录成功");
            }
            else {
                System.out.println("登录失败");
            }
        //7.关闭连接
        rs.close();
        pstmt.close();
        conn.close();
    }
}

开启预编译可以提高性能

数据库连接池

• 概念

  

• 表准接口

  

package com.yeluo.jdbc.druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

public class demo  {
    public static void main(String[] args) throws Exception {
        //1.导入jar包
        //2.定义配置

        //3.加载配置文件
        Properties prop=new Properties();
        prop.load(new FileInputStream("jdbc01/src/druid"));
        //4.获取连接池对象
        DataSource dataSource=DruidDataSourceFactory.createDataSource(prop);

        //5.获取数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
    }
}

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///demo01?useSSL=false&useSeerverPrepStmts=true
username=root
password=123456
#初始化连接数量
initialSize=5
#最大连接数
maxActive=10
#最大等待时间
maxWait=3000

案例

package com.yeluo.jdbc.example;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.yeluo.jdbc.pojo.Brand;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;
/*
* 品牌数据的增删改查
* */
public class BrandTest {
    public static void main(String[] args) throws  Exception {
        //1.获取connection连接


        //加载配置文件
        Properties prop=new Properties();
        prop.load(new FileInputStream("jdbc01/src/druid"));
        //4.获取连接池对象
        DataSource dataSource=DruidDataSourceFactory.createDataSource(prop);
        //5.获取数据库连接
        Connection conn = dataSource.getConnection();


        //2.定义sql
        String sql ="select *from tb_brand";

        //3.获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4.设置参数

        //5.执行sql
        ResultSet rs = pstmt.executeQuery();

        //6.处理结果

        Brand brand =null;
        List<Brand> brands=new ArrayList<>();
        while (rs.next()){
            //获取数据
            int id = rs.getInt("id");
            String brandName = rs.getString("brand_name");
            String companyName = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            int s_tatus = rs.getInt("s_tatus");

            //封装Brand集合
            brand =new Brand();
            brand.setId(id);
            brand.setBrand_name(brandName);
            brand.setCompany_name(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setS_tatus(s_tatus);
            //装载集合
            brands.add(brand);
        }
        System.out.println(brands);
        //7.关闭连接
        rs.close();
        pstmt.close();
        conn.close();


    }
}

Brand

package com.yeluo.jdbc.pojo;

public class Account {
    private int id;
    private String name;
    private  double money;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getMoney() {
        return money;
    }

    public void setMoney(double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }

}

uble getMoney() {
return money;
}

public void setMoney(double money) {
    this.money = money;
}

@Override
public String toString() {
    return "Account{" +
            "id=" + id +
            ", name='" + name + '\'' +
            ", money=" + money +
            '}';
}

}