JDBC连接数据库的基本模板

文章目录

• 前言
• 一、控制台Mysql代码
• 二、JavaWeb项目Mysql代码
• 注意

---

前言

具体应用，需要看具体的内容进行分析，不能生搬硬套。

---

提示：以下是一个JDBC连接数据库的例子

一、控制台Mysql代码

package org.example;

import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 *
 * @author : Future master
 * @version : 1.0
 * @Project : TestThree
 * @Package : org.example
 * @ClassName : OwnSql.java
 * @createTime : 2021/11/25 7:58
 * @Email : 2467636181@qq.com
 */
public class OwnSql {
    private Connection conn;
    private static String url = "jdbc:mysql://localhost:3306/dxz";
    private static String user = "root";
    private static String password = "root";
    //构造函数得到一个Connection
    public OwnSql() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(url,user,password);
    }
    //新建一个创造表的statement
    public int getTable() throws SQLException {
        Statement statement1 = conn.createStatement();
        String sql1 = "create table if not exists c4(id int primary key auto_increment,filename varchar(64) not null default ''," +
                "key1 char(32) not null default '',iv char(16) not null default '');";
        int result = statement1.executeUpdate(sql1);
        statement1.close();
        return result;
    }
    public int dropTable() throws SQLException {
        Statement statement = conn.createStatement();
        String sql4 = "drop table if exists c4";
        int result = statement.executeUpdate(sql4);
        statement.close();
        return result;
    }
    //将数据输入到数据库之中
    public int insertDate(String name,String key1,String iv) throws SQLException {
        String sql2 = "insert into c4(filename,key1,iv) values('"+name+"','"+key1+"','"+iv+"');";
        Statement statement2  = conn.createStatement();
        int result = statement2.executeUpdate(sql2);
        statement2.close();
        return result;
    }
    //查询所查询的文件是否存在
    public boolean isExist(String name) throws SQLException {
        boolean result = false;
        String sql3 = "select filename from c4";
        Statement statement = conn.createStatement();
        ResultSet set = statement.executeQuery(sql3);
        while(set.next()){
            if(set.getString(1).equals(name))result = true;
        }
        return result;
    }
}

二、JavaWeb项目Mysql代码

package com.c4;

import javax.swing.plaf.nimbus.State;
import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 *
 * @author : Future master
 * @version : 1.0
 * @Project : Web3
 * @Package : PACKAGE_NAME
 * @ClassName : com.c4.OwnSql.java
 * @createTime : 2021/11/25 21:20
 * @Email : 2467636181@qq.com
 */
public class OwnSql {
    public Connection conn;
    private String Url = "jdbc:mysql://localhost:3306/dxz";
    private String User = "root";
    private String Password = "root";
    //构造函数创建connection
    public OwnSql(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(Url,User,Password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //如果表结构不存在则直接创建表
    public int getTable() throws SQLException {
        Statement statement = conn.createStatement();
        String sql1 = "create table if not exists web3(id int primary key auto_increment," +
                "name varchar(32) not null default '',sex char(4) not null default '男'," +
                "age int not null default 0);";
        int result = statement.executeUpdate(sql1);

        return result;
    }
    //在表中插入数据
    public int insertDate(String name,String sex,int age) throws SQLException {
        String sql2 = "insert into web3(name,sex,age) values(?,?,?);";
        PreparedStatement statement = conn.prepareStatement(sql2);
        statement.setString(1,name);
        statement.setString(2,sex);
        statement.setInt(3,age);
        int result = statement.executeUpdate();

        return result;
    }
    //根据id去得到成员的名字性别姓名
    public ResultSet getDateById(int id) throws SQLException {
        String sql3 = "select name,sex,age from web3 where id=?";
        PreparedStatement statement = conn.prepareStatement(sql3);
        statement.setInt(1,id);
        ResultSet result = statement.executeQuery();

        return result;
    }
    //根据成员的id对成员信息进行修改
    public int updateDate(int id, String name, String sex, int age) throws SQLException {
        String sql4 = "update web3 set name=?,sex=?,age=? where id=?;";
        PreparedStatement statement = conn.prepareStatement(sql4);
        statement.setString(1,name);
        statement.setString(2,sex);
        statement.setInt(3,age);
        statement.setInt(4,id);
        int result = statement.executeUpdate();
        return result;
    }
    //判断表是否为空
    public boolean empty() throws SQLException {
        boolean result = true;
        String sql5 = "select name from web3";
        Statement statement = conn.createStatement();
        ResultSet set = null;
        set = statement.executeQuery(sql5);
        if(set.next()){result = false;}

        return result;
    }
    //获取成员数据总数
    public int getNums() throws SQLException {
        int result = 0;
        String sql6 = "select id from web3";
        Statement statement = conn.createStatement();
        ResultSet set = statement.executeQuery(sql6);
        while(set.next()){
            result++;
        }

        return result;
    }
    //得到所有的数据成员
    public ResultSet getUsers() throws SQLException {
        String sql7 = "select id,name,sex,age from web3";
        Statement statement = conn.createStatement();
        ResultSet set = statement.executeQuery(sql7);
        return set;
    }
}

---

注意

• 在linux中数据库的代码是区分大小写的
• 返回的ReasultSet可以通过set.next()方法进行判断有没有数据，而且如果要使用set中的内容的话也一定要先调用set.next()方法。
• Statement可以有两种创建方法。

1. PreparedStatement statement = conn.prepareStatement(sql);
2. Statement statement = conn.createStatement();

• Connection 创建的时候一定要先加载Class.forName("com.mysql.cj.jdbc.Driver");
             
• 然后在进行创建Connection conn = DriverManager.getConnection(Url,User,Password);