JDBC汇总

已于 2023-11-11 18:18:55 修改
阅读量160 收藏
点赞数
分类专栏： jdbc 文章标签： java sql 数据库
于 2022-09-22 11:15:04 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_53415999/article/details/126987842
版权 
package Package8.Sql;

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

public class connection {
    public static void main(String[] args) throws Exception {
        //Connecton接口代表Java程序和数据库的连接，只有获得该链接才能访问、操作数据库。
        //Statement接口用于执行静态的sql语句，并返回一个结果对象。用于执行预编译的SQL语句接口扩展了带有参数SQL语句的执行操作
        //ResuliSet接口用于保存JDBC执行查询时返回的结果集，该结果封装在一个逻辑表格中
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            //因为JDBC中是MYSQL专门提供的jar包，所以url都是一致的，后面的user和password是根据数据库那边自己设置的
            connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/hkzf?characterEncoding=UTF-8","root","root");
            statement=connection.createStatement();
            //这是一条查询的SQL语句
            resultSet=statement.executeQuery("select * from areas");
            //调用next()方法可以将游标移动到下一行，如果下一代没有数据，则返回false
            while(resultSet.next()){
                //注意这里打印输出的数据类型需要和数据库表中的数据一致，不然也会报错
                System.out.println(resultSet.getInt("id")+":"+resultSet.getString("city")+":"
                        +resultSet.getString("city_name")+":"+resultSet.getString("area")+":"+resultSet.getString("street"));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{//调用finally方法，强制执行下面三个方法，关闭数据流，节约内存
            resultSet.close();
            statement.close();
            connection.close();
        }
    }
}

查询符合sql的数据条数：

package com.huse.demo;

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

public class Jdbc_1 {


    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");

        String un = "root";
        String url = "jdbc:mysql://localhost:3306/cruddemo";//localhost = 127.0.0.1
        String pw = "root";
        Connection conn = DriverManager.getConnection(url,un,pw);

        String sql = "update user set city = '北京' where name = '王欢欢'";//注意双套单可以，单套双它不可以要记住啊
        Statement stmt = conn.createStatement();

        int count = stmt.executeUpdate(sql);

        System.out.println(count);

        //释放资源
        stmt.close();
        conn.close();
    }


}

利用jdbc更新数据：

package com.huse.demo;

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

public class Jdbc_2 {


    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");

        String un = "root";
        String url = "jdbc:mysql://localhost:3306/cruddemo?useSSL=false";//localhost = 127.0.0.1
        String pw = "root";
        Connection conn = DriverManager.getConnection(url,un,pw);

        String sql = "update user set city = '北京' where name = '王欢'";//注意双套单可以，单套双它不可以要记住啊
        Statement stmt = conn.createStatement();

        try {
            //开启事务
            conn.setAutoCommit(false);
            int count = stmt.executeUpdate(sql);
            System.out.println(count);

            //提交事务
            conn.commit();
        } catch (Exception e) {

            //回滚事务
            e.printStackTrace();
        }

        //释放资源
        stmt.close();
        conn.close();
    }
}

查询所有符合sql的信息以及符合条数：

package com.huse.demo;

import org.testng.annotations.Test;

import java.sql.*;

public class Jdbc_3 {

    @Test
    public void ResultSet() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");

        String un = "root";
        String url = "jdbc:mysql://localhost:3306/cruddemo";//localhost = 127.0.0.1
        String pw = "root";
        Connection conn = DriverManager.getConnection(url, un, pw);

        String sql = "select * from user";

        Statement stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {

            int id = rs.getInt(1);//getInt(id);
            String name = rs.getString(2);//getString(name);
            int city = rs.getInt(3);

            System.out.println(id);
            System.out.println(name);
            System.out.println(city);
        }

        rs.close();
        stmt.close();
        conn.close();
//
//        while(rs.next()){
//            int id = rs.getInt(1);
//            String name = rs.getString(2);
//            int city = rs.getInt(3);
//
            for (String v:name){

            }
//
//            System.out.println(id);
//            System.out.println(name);
//            System.out.println(city);
//
//        }
//
//        //释放资源
//        rs.close();
//        stmt.close();
//        conn.close();
//    }
    }
}

查询所有符合sql的信息以及符合条数并且放到一个容器中：

package com.huse.demo;

import com.huse.demo.project.Acount;
import org.testng.annotations.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class Jdbc_Test {

    @Test
    public void ResultSet() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");

        String un = "root";
        String url = "jdbc:mysql://localhost:3306/cruddemo";//localhost = 127.0.0.1
        String pw = "root";
        Connection conn = DriverManager.getConnection(url, un, pw);

        String sql = "select * from user";

        Statement stmt = conn.createStatement();

        ResultSet rs = stmt.executeQuery(sql);

        List<Acount> list = new ArrayList<>();

        while (rs.next()) {

            //每每查询一个数据 建立一个对象
            Acount account = new Acount();


            int id = rs.getInt(1);//getInt(id);
            String name = rs.getString(2);//getString(name);
            int city = rs.getInt(3);

            System.out.println(id);
            System.out.println(name);
            System.out.println(city);

            //存入集合
            list.add(account);
        }
        //应用：通过其他方式将这个对象中数据传到页面当中去，页面只需要遍历便可以得到全部数据

        System.out.println(list);

        rs.close();
        stmt.close();
        conn.close();

    }
}

使用Druid实现增删改查：

 记得导入Mysql和druids包

 注意最下面有一个配置文件：druid.properties

driverClassName=com.mysql.jdbc.Driver
#url=jdbc:mysql://127.0.0.1:3306/cruddemo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
url=jdbc:mysql://127.0.0.1:3306/brand
username=root
password=root
#???????
initialSize=5
#?????
maxActive=10
maxWait=3000

druids.java

driverClassName=com.mysql.jdbc.Driver
#url=jdbc:mysql://127.0.0.1:3306/cruddemo?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
url=jdbc:mysql://127.0.0.1:3306/brand
username=root
password=root
#???????
initialSize=5
#?????
maxActive=10
maxWait=3000

查询类:

package com.huse.exercise;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.huse.demo.project.Brand;
import org.testng.annotations.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 做品牌的增删改查操作
 *下午 com.alibaba.druid.pool.DruidDataSource error 严重: dataSource init error：错误记录->版本问题1.0.9换1.1.12
 */

public class BrandTest {

    //获取Connection(固定)
    //定义SQL
    //获取PreparedStatement对象(固定)
    //设置参数
    //执行SQL(固定)
    //处理结果封装成Brand对象:List<Brand>
    //释放资源(固定)


    @Test
    public void testSelectAll() throws Exception {

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

        String sql = "select * from tb_brand";

        PreparedStatement pstmt = conn.prepareStatement(sql);


        ResultSet rs = pstmt.executeQuery();
        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 status = rs.getInt("status");
            //封装Brand对象
            brand = new Brand();
            brand.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);
            //封装集合
            brands.add(brand);

        }
        System.out.println(brands);

        rs.close();
        pstmt.close();
        conn.close();

    }












}

增加数据：

package com.huse.exercise;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.huse.demo.project.Brand;
import org.testng.annotations.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 做品牌的增删改查操作
 *下午 com.alibaba.druid.pool.DruidDataSource error 严重: dataSource init error：错误记录->版本问题1.0.9换1.1.12
 */

public class BrandTest1 {

    //获取Connection(固定)
    //定义SQL
    //获取PreparedStatement对象(固定)
    //设置参数
    //执行SQL(固定)
    //处理结果封装成Brand对象:List<Brand>
    //释放资源(固定)


    @Test
    public void testAdd() throws Exception {
        //接受页面提交的参数
        String brandName  = "优乐美";
        String companyName  = "优乐美";
        int ordered = 1;
        String description = "真好喝";
        int status = 1;


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

        String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values (?,?,?,?,?)";

        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置参数
        pstmt.setString(1,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);

//执行SQL
        int count = pstmt.executeUpdate();//影响的行数
        //处理结果输出
        System.out.println(count > 0);
        System.out.println(count);
//        ResultSet rs = pstmt.executeQuery();
//
//        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 status = rs.getInt("status");
//            //封装Brand对象
//            brand = new Brand();
//            brand.setId(id);
//            brand.setBrandName(brandName);
//            brand.setCompanyName(companyName);
//            brand.setOrdered(ordered);
//            brand.setDescription(description);
//            brand.setStatus(status);
//            //封装集合
//            brands.add(brand);
//
//        }
//        System.out.println(brands);


        pstmt.close();
        conn.close();

    }












}

修改操作：

package com.huse.exercise;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.testng.annotations.Test;

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

/**
 * 做品牌的增删改查操作  这里是根据id进行修改
 *下午 com.alibaba.druid.pool.DruidDataSource error 严重: dataSource init error：错误记录->版本问题1.0.9换1.1.12
 */

public class BrandTest2 {

    //获取Connection(固定)
    //定义SQL
    //获取PreparedStatement对象(固定)
    //设置参数
    //执行SQL(固定)
    //处理结果封装成Brand对象:List<Brand>
    //释放资源(固定)
//    update tb_brand
//            set brand_name = ?,
//            company_name = ?,
//            ordered = ?,
//            description = ?,
//            status = ?
//    where id = ?
//    返回boolean


    @Test
    public void testUpdate() throws Exception {
        //接受页面提交的参数
        String brandName  = "优乐美";
        String companyName  = "优乐美";
        int ordered = 10086;
        String description = "真好喝,真tm好喝呀";
        int status = 1;
        int id = 7;


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

        String sql = "    update tb_brand\n" +
                "            set brand_name = ?,\n" +
                "            company_name = ?,\n" +
                "            ordered = ?,\n" +
                "            description = ?,\n" +
                "            status = ?\n" +
                "    where id = ?";

        PreparedStatement pstmt = conn.prepareStatement(sql);

        //设置参数
        pstmt.setString(1,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);
        pstmt.setInt(6,id);

//执行SQL
        int count = pstmt.executeUpdate();//影响的行数
        //处理结果输出
        System.out.println(count > 0);
        System.out.println(count);
//        ResultSet rs = pstmt.executeQuery();
//
//        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 status = rs.getInt("status");
//            //封装Brand对象
//            brand = new Brand();
//            brand.setId(id);
//            brand.setBrandName(brandName);
//            brand.setCompanyName(companyName);
//            brand.setOrdered(ordered);
//            brand.setDescription(description);
//            brand.setStatus(status);
//            //封装集合
//            brands.add(brand);
//
//        }
//        System.out.println(brands);


        pstmt.close();
        conn.close();

    }












}

删除：

package com.huse.exercise;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.testng.annotations.Test;

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

/**
 * 做品牌的增删改查操作  这里是根据id进行删除
 *下午 com.alibaba.druid.pool.DruidDataSource error 严重: dataSource init error：错误记录->版本问题1.0.9换1.1.12
 */

public class BrandTest3 {

    //获取Connection(固定)
    //定义SQL
    //获取PreparedStatement对象(固定)
    //设置参数
    //执行SQL(固定)
    //处理结果封装成Brand对象:List<Brand>
    //释放资源(固定)

//    sql:delete from tb_brand where id = ?


    @Test
    public void testDeleteById() throws Exception {
        //接受页面提交的参数
        int id = 8;


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

        String sql = "delete from tb_brand where id = ?";

        PreparedStatement pstmt = conn.prepareStatement(sql);//PreparedStatement防止SQL注入问题

        //设置参数
        pstmt.setInt(1,id);

        int count = pstmt.executeUpdate();//影响的行数
        //处理结果输出
        System.out.println(count > 0);
        System.out.println(count);


        //释放资源
        pstmt.close();
        conn.close();

    }












}

Druid_1.java

package com.huse.druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

public class Druid_1 {
    public static void main(String[] args) throws Exception {
        //导入 定义 加载 获取 连接

        Properties prop = new Properties();
        prop.load(new FileReader("D:/javaWeb暑假/src/druid.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        Connection conn = dataSource.getConnection();
        System.out.println(conn);


    }
}

jdbc_01.iml：

<?xml version="1.0" encoding="UTF-8"?>
<module type="JAVA_MODULE" version="4">
  <component name="NewModuleRootManager" inherit-compiler-output="true">
    <exclude-output />
    <content url="file://$MODULE_DIR$">
      <sourceFolder url="file://$MODULE_DIR$/src" isTestSource="false" />
    </content>
    <orderEntry type="inheritedJdk" />
    <orderEntry type="sourceFolder" forTests="false" />
    <orderEntry type="module-library">
      <library>
        <CLASSES>
          <root url="jar://$MODULE_DIR$/lib/mysql-connector-java-8.0.29.jar!/" />
        </CLASSES>
        <JAVADOC />
        <SOURCES />
      </library>
    </orderEntry>
    <orderEntry type="module-library">
      <library name="testng">
        <CLASSES>
          <root url="jar://$MAVEN_REPOSITORY$/org/testng/testng/7.1.0/testng-7.1.0.jar!/" />
          <root url="jar://$MAVEN_REPOSITORY$/com/beust/jcommander/1.72/jcommander-1.72.jar!/" />
          <root url="jar://$MAVEN_REPOSITORY$/com/google/inject/guice/4.1.0/guice-4.1.0-no_aop.jar!/" />
          <root url="jar://$MAVEN_REPOSITORY$/javax/inject/javax.inject/1/javax.inject-1.jar!/" />
          <root url="jar://$MAVEN_REPOSITORY$/aopalliance/aopalliance/1.0/aopalliance-1.0.jar!/" />
          <root url="jar://$MAVEN_REPOSITORY$/com/google/guava/guava/19.0/guava-19.0.jar!/" />
          <root url="jar://$MAVEN_REPOSITORY$/org/yaml/snakeyaml/1.21/snakeyaml-1.21.jar!/" />
        </CLASSES>
        <JAVADOC />
        <SOURCES />
      </library>
    </orderEntry>
    <orderEntry type="module-library">
      <library>
        <CLASSES>
          <root url="jar://$MODULE_DIR$/lib/druid-1.0.9.jar!/" />
        </CLASSES>
        <JAVADOC />
        <SOURCES />
      </library>
    </orderEntry>
    <orderEntry type="module-library">
      <library>
        <CLASSES>
          <root url="jar://$MODULE_DIR$/lib/druid-1.1.12.jar!/" />
        </CLASSES>
        <JAVADOC />
        <SOURCES />
      </library>
    </orderEntry>
  </component>
</module>
json{shen:"jing"}
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值