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);
	}

}