Java连接数据库与常用方法(java连接数据库的四种方式)
2022-03-01
Java连接数据库
创建java项目:new->java project->(JRE处) Use a project specific JRE->将驱动放到根目录
建立依赖:右键->proterties->library->add jar->将项目的jar包加进来
java连接数据库
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/* *JDBC JAVA DATA BASE Connectivity(Java数据库连接) *SSL(Secure Scokets Layer安全套接字协议) *TLS(Transport Layer Security,TLS) 继任者传输层安全 *是为网络通信提供安全及数据完整性的一种安全协议。TSL与SSL在传输层与应用层之间对网络连接进行加密 */
import java.sql.SQLException;
public class Conn {
// 数据库地址url
private static final String URL = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false";
// 数据库账户名
private static final String USER = "root";
// 数据库账户密码
private static final String PASS = "123456";
// 数据库连接类
static Connection conn = null;
// 接收查询返回的结果集
static ResultSet rs = null;
// 预编译SQL语句的对象
static PreparedStatement ps = null;
// 连接初始化
public static void init() {
if (conn == null) {
try {
// 反射获取连接驱动类,根据包路径加载连接驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
// 建立连接
conn = DriverManager.getConnection(URL, USER, PASS);
} catch (Exception e) {
System.out.println("database connection fail");
e.printStackTrace();
}
System.out.println("*****build connection success****");
} else {
System.out.println("*****already build connection success****");
}
}
public static void main(String[] args) {
// 建立连接
Conn.init();
/*//查询 String sql="select * from stu_info"; try { //预编译sql ps=conn.prepareStatement(sql); //执行sql rs=ps.executeQuery(sql); while(rs.next()) { System.out.println(rs.getInt("id")+","+rs.getString("stu_name")); } } catch (Exception e) { System.out.println("sql执行失败"); }*/
//update/insert/delete
String sql = "insert into stu_info values(14,'熊大',1,3,100)";
try {
ps = conn.prepareStatement(sql);
// 返回执行sql影响的行数
int re = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("sql执行失败");
}
}
}
常用方法
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/* *JDBC JAVA DATA BASE Connectivity(Java数据库连接) *SSL(Secure Scokets Layer安全套接字协议) *TLS(Transport Layer Security,TLS) 继任者传输层安全 *是为网络通信提供安全及数据完整性的一种安全协议。TSL与SSL在传输层与应用层之间对网络连接进行加密 */
import java.sql.SQLException;
import java.sql.Statement;
public class Conn {
// 数据库地址url
private static final String URL = "jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false";
// 数据库账户名
private static final String USER = "root";
// 数据库账户密码
private static final String PASS = "123456";
// 数据库连接类
static Connection conn = null;
// 接收查询返回的结果集
static ResultSet rs = null;
// 预编译SQL语句的对象
static PreparedStatement ps = null;
// 1.连接初始化
public static void init() {
if (conn == null) {
try {
// 反射获取连接驱动类,根据包路径加载连接驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
// 建立连接
conn = DriverManager.getConnection(URL, USER, PASS);
} catch (Exception e) {
System.out.println("database connection fail");
e.printStackTrace();
}
System.out.println("*****build connection success****");
} else {
System.out.println("*****already build connection success****");
}
}
// 2.查询
public void query(String sql) {
// 建立连接
Conn.init();
// 查询
try {
// 预编译sql
ps = conn.prepareStatement(sql);
// 执行sql
rs = ps.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getInt("id") + "," + rs.getString("stu_name"));
}
} catch (Exception e) {
System.out.println("sql执行失败");
}
}
// 3.update/insert/delete
public void execute() {
String sql = "insert into stu_info values(14,'熊大',1,3,100)";
try {
ps = conn.prepareStatement(sql);
// 返回执行sql影响的行数
int re = ps.executeUpdate();
} catch (SQLException e) {
System.out.println("sql执行失败");
}
}
// 4.占位符(?)插入
public static boolean add(String name, int sex) {
Conn.init();
String sql = "insert into stu_info(stu_name,sex)values(?,?)";
try {
ps = conn.prepareStatement(sql);
// 替换指定位置的占位符
ps.setString(1, name);
ps.setInt(2, sex);
// execute()执行preparedStatement对象中的sql语句,该对象可以是任何类型的sql语句
return ps.execute();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
// 5.批量插入
public static int dealBatch(String[] sqls) {
Conn.init();
try {
// 设置事务自动提交,jdbc中默认true
conn.setAutoCommit(false);
Statement st = conn.createStatement();
for (int i = 0; i < sqls.length; i++) {
st.addBatch(sqls[i]);
}
// 批量执行
st.executeBatch();
// 提交事务
conn.commit();
return 1;
} catch (Exception e) {
e.printStackTrace();
return 0;
}
}
// 6.插入的同时返回新插入语句的主键
public static Integer addReturnPrimaryKey(String sql) {
Conn.init();
try {
// 设置执行完插入操作需要返回主键
ps = conn.prepareStatement(sql, ps.RETURN_GENERATED_KEYS);
// 执行sql影响的行数
int re = ps.executeUpdate();
if (re == 0) { // sql执行失败
return 0;
}
// 获取新插入记录的主键
ResultSet rs2 = ps.getGeneratedKeys();
Integer id = null;
if (rs2.next()) { // 遍历结果集,向下读一行数据
// 获取第一个字段的值
id = rs2.getInt(1);
}
return id;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
// 7.关闭
public static void close() {
if (rs != null)
rs = null;
if (ps != null)
ps = null;
if (conn != null)
conn = null;
}
public static void main(String[] args) {
// 批量插入
String[] sqls = new String[2];
sqls[0] = "insert into stu_info set stu_name='刘备'";
sqls[1] = "insert into stu_info set stu_name='张飞'";
int code = dealBatch(sqls);
System.out.println(code);
// 占位符
add("小飞", 1);
// 返回当前插入记录的主键
String sql = "insert into stu_info set stu_name='关羽'";
Integer key = addReturnPrimaryKey(sql);
System.out.println(key);
}
}