Java JDBC 连接Mysql数据库

时间：2020-12-16 13:05:03

以下Code在同一个Package中。亲测可以进行CURD操作。

JDBC的JdbcUtils类，例如：

package JDBC_Java_Mysql;

import java.sql.*;

/**
 * Created by dhc on 17-5-2. 
 * @author: 网络黑寡妇
 */
public class JdbcUtils{
 //获取数据库连接驱动
private static String driver = "com.mysql.jdbc.Driver"; 
 //获取数据库连接用户名
private static String username = "root";
 //获取数据库连接密码
private static String password = "123456";
 //获取数据库连接URL地址
private static String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8";

 static {
 try {
 //加载数据库驱动
 Class.forName(driver);
 } catch (Exception e) {
 throw new ExceptionInInitializerError(e);
 }
 }

 /**
 * @return Connection数据库连接对象
 * @Description: 获取数据库连接对象
 */
 public static Connection getConnection() throws SQLException {
 return DriverManager.getConnection(url, username, password);
 }

 /**
 * @Description: 释放资源，
 * 要释放的资源包括Connection数据库连接对象，负责执行SQL命令的Statement对象，存储查询结果的ResultSet对象
 */
 public static void release(Connection conn, Statement st, ResultSet rs) {
 if (rs != null) {
 try {
 //关闭存储查询结果的ResultSet对象
 rs.close();
 } catch (Exception e) {
 e.printStackTrace();
 }
 }
 if (st != null) {
 try {
 //关闭负责执行SQL命令的Statement对象
 st.close();
 } catch (Exception e) {
 e.printStackTrace();
 }
 }

 if (conn != null) {
 try {
 //关闭Connection数据库连接对象
 conn.close();
 } catch (Exception e) {
 e.printStackTrace();
 }
 }
 }
}

代码块

JdbcCRUDByStatement类利用Statement对象操作数据库（建议使用PreparedStatement对象操作），例如：

package JDBC_Java_Mysql;

import java.sql.*;

/**
 * Created by dhc on 17-5-2. 
 * @ClassName: JdbcCRUDByStatement
 * @Description: 通过Statement对象完成对数据库的CRUD操作
 * @author: 网络黑寡妇
 */
public class JdbcCRUDByStatement{
private static Connection conn = null;
private static Statement st = null;
private static ResultSet rs = null;

public void insert(String strSql) {
try {
 //获取一个数据库连接
conn = JdbcUtils.getConnection();
 //通过conn对象获取负责执行SQL命令的Statement对象
st = conn.createStatement();
 //执行插入操作，executeUpdate方法返回成功的条数
int num = st.executeUpdate(strSql);
if (num > 0) {
System.out.println("插入成功！！");
 }
 } catch (Exception e) {
e.printStackTrace();
 } finally {
 //SQL执行完成之后释放相关资源
JdbcUtils.release(conn, st, rs);
 }
 }


public void delete(String strSql) {
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
int num = st.executeUpdate(strSql);
if (num > 0) {
System.out.println("删除成功！！");
 }
 } catch (Exception e) {
e.printStackTrace();
 } finally {
JdbcUtils.release(conn, st, rs);
 }
 }


public void update(String strSql) {
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
int num = st.executeUpdate(strSql);
if (num > 0) {
System.out.println("更新成功！!！");
 }
 } catch (Exception e) {
e.printStackTrace();
 } finally {
JdbcUtils.release(conn, st, rs);
 }
 }

public void find(String strSql) {
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
rs = st.executeQuery(strSql);
while (rs.next()) {
System.out.print(rs.getString("userid") + " ");
System.out.print(rs.getString("name") + " ");
System.out.print(rs.getString("sex") + " ");
System.out.println();
 }
 } catch (Exception e) {
e.printStackTrace();
 } finally {
JdbcUtils.release(conn, st, rs);
 }
 }
}

代码块

JdbcCRUDByPreparedStatement类利用PreparedStatement对象操作数据库（建议使用），例如：

package JDBC_Java_Mysql;

import java.sql.*;

/**
 * Created by dhc on 17-5-2.
 *
 * @ClassName: JdbcCRUDByPreparedStatement
 * @Description: 通过PreparedStatement对象完成对数据库的CRUD操作
 * @author: 网络黑寡妇
 */

/**
 * SQL语句中各个字段的类型如下：
 * +----------+-------------+
 * | Field    | Type        |
 * +----------+-------------+
 * | userid   | int(11)     |
 * | name     | varchar(40) |
 * | sex      | varchar(40) |
 * | Tel      | varchar(60) |
 * +----------+-------------+
 */

public class JdbcCRUDByPreparedStatement{
private static Connection conn = null;
private static PreparedStatement st = null;
private static ResultSet rs = null;

public void insert(String strSql) {
try {
 //获取一个数据库连接
conn = JdbcUtils.getConnection();
 //要执行的SQL命令，SQL中的参数使用?作为占位符
 //String strSql = "insert into users(name,sex) values(?,?)";
 //通过conn对象获取负责执行SQL命令的prepareStatement对象
st = conn.prepareStatement(strSql);
 //执行插入操作，executeUpdate方法返回成功的条数
int num = st.executeUpdate();
if (num > 0) {
System.out.println("插入成功！!！");
 }
 } catch (Exception e) {
e.printStackTrace();
 } finally {
 //SQL执行完成之后释放相关资源
JdbcUtils.release(conn, st, rs);
 }
 }

public void delete(String strSql) {
try {
conn = JdbcUtils.getConnection();
st = conn.prepareStatement(strSql);
int num = st.executeUpdate();
if (num > 0) {
System.out.println("删除成功！!！");
 }
 } catch (Exception e) {
e.printStackTrace();
 } finally {
JdbcUtils.release(conn, st, rs);
 }
 }

public void update(String strSql) {
try {
conn = JdbcUtils.getConnection();
st = conn.prepareStatement(strSql);
int num = st.executeUpdate();
if (num > 0) {
System.out.println("更新成功！！");
 }
 } catch (Exception e) {
e.printStackTrace();

 } finally {
JdbcUtils.release(conn, st, rs);
 }
 }

public void find(String strSql) {
try {
conn = JdbcUtils.getConnection();
st = conn.prepareStatement(strSql);
rs = st.executeQuery();
while (rs.next()) {
System.out.print(rs.getString("userid") + " ");
System.out.print(rs.getString("name") + " ");
System.out.print(rs.getString("sex") + " ");
System.out.print(rs.getString("Tel") + " ");
System.out.println();
 }
 } catch (Exception e) {
e.printStackTrace();
 } finally {
JdbcUtils.release(conn, st, rs);
 }
 }
}

代码块

对Mysql数据库的CURD操作测试Code，例如：

package JDBC_Java_Mysql;

/**
 * Created by dhc on 17-5-2.
 * 测试对数据库Mysql的CRUD操作。
 * @author: 网络黑寡妇
 */
public class Jdbc_test{
public static void main(String[] args) {
JdbcCRUDByPreparedStatement ps = new JdbcCRUDByPreparedStatement();
 //查询操作
String strSql = "select * from user";
ps.find(strSql);
 //JdbcCRUDByStatement ps1=new JdbcCRUDByStatement();
 //ps1.find(strSql);

 //删除操作
String delSql = "delete from user where userid='4' ";
 //ps.delete(delSql);

 //添加操作
String insertSql = "insert into user values(3,'xiaohong','w',156666669)";
 //ps.insert(insertSql);

 //更新操作
String updateSql = "update user set Tel=156666666 where userid=3";
 //ps.update(updateSql);
 }
}
标签：

相关文章