jdbc连接数据库

1 创建一个数据库,创建一个表

-- 创建数据库
create database epet;
-- 创建user表
create table user(
	int id,
	varchar(10) name,
	varchar(10) password
);
--插入数据
insert into user values(1,"root","123456");

• 数据库
  

2 创建java类,使用jdbc连接数据库,进行数据库查询操作

• 注意: 这里使用的是mysql8.0版本,使用的驱动是com.mysql.cj.jdbc.Driver,如果是mysql5.0版本使用的驱动为com.mysql.jdbc.Driver

package jdbc;
import java.sql.*;

public class SelectDB {
    private static final String name="root";
    private static final String password="123456";
    private static final String url="jdbc:mysql://localhost:3306/epet?serverTimezone=UTC";
    public static void main(String[] args) {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;
        try {
            System.out.println("加载数据库驱动..");
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(url,name,password);
            String sql ="select * from user";
            //创建statement对象
            st = conn.createStatement();
            //执行SQL语句
            rs = st.executeQuery(sql);
            //遍历查询结果
            System.out.println("id \t name \t age");
            while (rs.next()){
                //使用下标索引,定位到查询结果第一列
                int userId = rs.getInt(1);
                //使用列名索引,定位到查询结果name列
                String userName=rs.getString("name");
                String userPassword= rs.getString(3);
                System.out.println(userId+"\t"+userName+"\t"+userPassword);
            }
        } catch (Exception e) {
            System.out.println("数据库连接失败,查询失败!");
        } finally {
            /*
            默认执行块,数据库关闭顺序同打开顺序相反
             */
            if (rs!=null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (st!=null){
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

• 运行结果:
  

3 通过jdbc对数据库新增数据

package jdbc;

import java.sql.*;
public class SaveDB {
    private static final String name="root";
    private static final String password="123456";
    private static final String url="jdbc:mysql://localhost:3306/epet?serverTimezone=UTC";
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement st=null;
        try {
            System.out.println("加载数据库驱动..");
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(url,name,password);
            String sql ="insert into user(id,name,password) values(?,?,?)";
            st = conn.prepareStatement(sql);
            st.setInt(1,2);
            st.setString(2,"ll");
            st.setString(3,"123");
            boolean result = st.execute();
            System.out.println("新增成功");
        } catch (Exception e) {
            System.out.println("数据库连接失败,执行失败!");
        } finally {
            /*
            默认执行块,数据库关闭顺序同打开顺序相反
             */

            if (st!=null){
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

• 运行结果
  
  

4 通过jdbc连接数据库实现数据修改

package jdbc;

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

public class UpdateDB {
    private static final String name="root";
    private static final String password="123456";
    private static final String url="jdbc:mysql://localhost:3306/epet?serverTimezone=UTC";
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement st=null;
        try {
            System.out.println("加载数据库驱动..");
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(url,name,password);
            String sql ="update user set name=? ,password=? where id =?";
            st = conn.prepareStatement(sql);
            st.setString(1,"223");
            st.setString(2,"223");
            st.setInt(3,2);
            boolean result = st.execute();
            System.out.println("更新成功....");
        } catch (Exception e) {
            System.out.println("数据库连接失败,执行失败!");
        } finally {
            /*
            默认执行块,数据库关闭顺序同打开顺序相反
             */

            if (st!=null){
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

• 执行结果
  
  

5 通过jdbc连接数据库删除数据,根据条件

package jdbc;

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

/**
 * @author ZRY
 * @version 1.0
 */
public class DeleteDB {
    private static final String name="root";
    private static final String password="123456";
    private static final String url="jdbc:mysql://localhost:3306/epet?serverTimezone=UTC";
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement st=null;
        try {
            System.out.println("加载数据库驱动..");
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(url,name,password);
            String sql ="delete from user where id=?";
            st = conn.prepareStatement(sql);
            st.setInt(1,2);
            boolean result = st.execute();
            System.out.println("删除成功....");
        } catch (Exception e) {
            System.out.println("数据库连接失败,执行失败!");
        } finally {
            /*
            默认执行块,数据库关闭顺序同打开顺序相反
             */
            if (st!=null){
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

• 运行结果