Java 连接 MySQL 需要驱动包，可以下载的 jar 包：http://static.runoob.com/download/mysql-connector-java-5.1.39-bin.jar
操作方法可分为三步：
一、配置程序——让我们程序能找到数据库的驱动jar包
1.把.jar文件复制到项目中去。
2.在eclipse项目右击“构建路径”--“配置构建路径”--“库”--“添加外部jar”--找到数据库的驱动jar包--点击确定。会在左侧包资源管理器中出现“引用的库”，在里面就能找到我们刚才导入的jar包
二、新建数据库和表
1.打开MySQL，点击左上角“连接”，输入连接名，确定。（密码可为空）
2.在建立的连接上点击鼠标右键，选择“创建数据库”，在弹出的窗口输入数据库名，字符集选择：utf8 -- UTF-8 Unicode
3.在新建立的数据库mydb上双击鼠标左键，图标变绿色，展开后点击“创建表”
4.输入相关数据并保存，保存是输入表名
5.打开表，输入数据，输入完毕后关闭，会自动保存数据
三、运行Java连接数据库并作相关操作
分为三个步骤
1.加载数据访问驱动
Class.forName("com.mysql.jdbc.Driver");
在左侧包资源管理器中出现“引用的库”，在里面就能找到Driver包
2.连接到数据"库"上去
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb","root","");
DriverManager:驱动管理器类，getConnection（url,数据库登录名,密码）：获得连接的方法
jdbc:mysql://localhost:3306/mydb的格式由来
jdbc:mysql://本机地址：端口号/数据库名称
若往数据库表中添加中文，url需改为：
jdbc:mysql://127.0.0.1:3306/mydb?characterEncoding=GBK
3.构建执行SQL命令
Statement state = conn.createStatement();
state.executeUpdate("增删改的sql语句");
state.executeQuery("查询的sql语句");
conn.close();//关闭连接
实例：往xs表里添加学号：008，姓名：李丽，学校：四中
import java.sql.*;
public class Test2 {
public static void main(String[] args) throws Exception {
// 1.加载数据访问驱动
Class.forName("com.mysql.jdbc.Driver");
//2.连接到数据"库"上去
Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?characterEncoding=GBK", "root", "");
//3.构建SQL命令
Statement state=conn.createStatement();
String s="insert into xs values('008','李丽','四中')";
state.executeUpdate(s);
}
}
1、驱动：配置pom
2、测试单链接
3、使用dbcp数据池连接
1、驱动：配置pom
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.46</version>
</dependency>
<!--microsoft acess数据库连接器 ucanaccess注意使用的版本问题 -->
<dependency>
<groupId>net.sf.ucanaccess</groupId>
<artifactId>ucanaccess</artifactId>
<version>4.0.1</version>
</dependency>
2、测试单链接：
public static void sqltest() throws ClassNotFoundException, SQLException {
//1、加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2、用户信息和url
String url = "jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=utf8&useSSL=false";
String name = "root";
String password = "shiyw11shuzi";
//3、连接成功，数据库对象 connection代表数据库
Connection connection = DriverManager.getConnection(url, name, password);
//4、执行sql的对象 statement 执行sql的对象
Statement statement = connection.createStatement();
//5、执行sql的对象去执行sql，可能存在返回结果，查看返回结果
String sql = "select * from `mingxi`";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.print("id = " + resultSet.getObject("id")+" ; ");
System.out.print("name = " + resultSet.getObject("name")+" ; ");
System.out.println("mima = " + resultSet.getObject("mima")+" ; ");
}
//6、关闭连接
resultSet.close();
statement.close();
connection.close();
System.out.println( MySQLConnTest.class.getResource("/").getPath() + "druid.properties" );
}
3、使用dbcp数据池连接:
项目主目录下的配置文件：D:IntelliJIDEA20workspacemysqltestdbmysql.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/book?useUnicode=true&useSSL=false&characterEncoding=utf8
username=rootbook
password=book2020shuzihua
4、使用dbcp数据池连接数据池:
import java.io.*;
import java.sql.*;
import java.util.Properties;
public class MysqlJDBCUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
String propfile = System.getProperty("user.dir")+File.separator+ "dbmysql.properties";
System.out.println( 33+MysqlJDBCUtils.class.getResource("/").getPath() + "dbmysql.properties");
System.out.println( 66+propfile);
//// 66D:IntelliJIDEA2018ideawork2020ddp2023padbmysql.properties
InputStream is = new BufferedInputStream(new FileInputStream(propfile));
//// InputStream error = MysqlJDBCUtils.class.getClassLoader().getResourceAsStream("dbmysql.properties");
Properties properties = new Properties();
properties.load(is);
driver = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
//1、加载驱动 只加载一次
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() {
////return DriverManager.getConnection(url, username, password);
Connection getConn=null;
try {
getConn=DriverManager.getConnection(url, username, password);
}catch (Exception e){ }
return getConn;
}
//释放连接资源
public static void release(Connection conn, Statement st, ResultSet rs) {
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();
}
}
}
5、链接测试：
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class JDBCUtilsTestSelect {
public static void main(String[] args) {
/////////// testConn(); ////////获取数据库连接///////
Connection conn = JDBCUtils.getConnection();
conn = MysqlJDBCUtils.getConnection();
testConn(conn);
}
private static void testConn(Connection conn) {
//// Connection conn = JDBCUtils.getConnection(); ////获取数据库连接 testConn(conn); ////
PreparedStatement st = null;
ResultSet rs = null;
try {
///// Connection conn = JDBCUtils.getConnection(); //获取数据库连接
String sql = "select * from `mingxi` where `id` = ?";
sql = "select * from temp ";
//预编译sql但不执行
st = conn.prepareStatement(sql);
//手动设置sql参数 st.setInt(1, 3);
//执行sql
rs = st.executeQuery();
while (rs.next()){
System.out.print("name = " + rs.getObject("name") +" " );
System.out.print("mima = " + rs.getObject("mima") +" " );
System.out.println("author = " + rs.getObject("author") +" " );
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//释放资源
JDBCUtils.release(conn, st, rs);
}
}
private static void testConn() {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection(); //获取数据库连接
String sql = "select * from `mingxi` where `id` = ?";
sql = "select * from temp ";
//预编译sql但不执行
st = conn.prepareStatement(sql);
//手动设置sql参数 st.setInt(1, 3);
//执行sql
rs = st.executeQuery();
while (rs.next()){
System.out.print("name = " + rs.getObject("name") +" " );
System.out.print("mima = " + rs.getObject("mima") +" " );
System.out.println("author = " + rs.getObject("author") +" " );
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//释放资源
JDBCUtils.release(conn, st, rs);
}
}
