Eclipse连接MySQL数据库与编程测试
一、工具
- Eclipse(默认已经装好,并且环境配置好)
- Mysql(默认装好)
- jdbc(mysql-connector-java-5.1.45-bin.jar)
二、 jdbc配置
1 下载mysql-connector-java-5.1.45-bin.jar
- 下载后解压
- 打开Eclipse
- window–>Preferences–>java–>Build path–>user Libraries
- 创建项目。
- 右击项目–>Build path–>Configure build path
- 至此已成功
三、JDBC原理
四、JDBC编程六部曲
需要导入的包
- import java.sql.Driver;
- import java.sql.DriverManager;
- import java.sql.Connection;
- import java.sql.Statement;
- import java.sql.ResultSet;
1. 注册驱动(后面第七点有其他方式,此方式不是常用方式)
1.1 获取驱动对象
Driver driver = new com.mysql.jdbc.Driver();
1.2 注册驱动
DriverManager.registerDriver(driver);
2. 获取数据库连接
String url = "jdbc:mysql://localhost:3306/数据库名称";
String user = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);//打印对象,打印出来即连接成功
3. 获取数据库操作对象
Statement stmt = conn.createStatement();
System.out.println(stmt);//打印对象
4. 执行SQL语句
String sql = "SQL语句";
ResultSet rs = stmt.executeQuery(sql);
System.out.println(rs);//打印对象
5. 处理查询结果集
while(rs.next()){
String ename = rs.getString("ename");
double sal = rs.getDouble("sal");
int grade = rs.getInt("grade");
System.out.println(ename + " " + sal + " " + grade);
}
6. 关闭资源
if(rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if(stmt != null) {
try {
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
五、示例执行DQL语句
package Test;
//导包
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
public class JDBCTest01 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
//1. 注册驱动
//1.1 获取驱动对象
Driver driver = new com.mysql.jdbc.Driver();
//1.2 注册驱动
DriverManager.registerDriver(driver);
//2. 获取数据库连接
String url = "jdbc:mysql://localhost:3306/dany";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);//打印对象,打印出来即连接成功
//3. 获取数据库操作对象
stmt = conn.createStatement();
System.out.println(stmt);//打印对象
//4. 执行DQL语句
String sql = "select e.ename,e.sal,s.grade "
+ "from emp e "
+ "join salgrade s "
+ "on e.sal between s.losal and s.hisal";
rs = stmt.executeQuery(sql);
System.out.println(rs);//打印对象
//5. 处理查询结果集
while(rs.next()){
// String ename = rs.getString("ename");
// double sal = rs.getDouble("sal");
// int grade = rs.getInt("grade");
//或者
String ename = rs.getString(1);
double sal = rs.getDouble(2);
int grade = rs.getInt(3);
System.out.println(ename + " " + sal + " " + grade);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//6. 关闭资源
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
六、示例执行DML语句
package Test;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
public class JDBCTest02 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
int count = 0;
try {
//1. 注册驱动
//1.1 获取驱动对象
Driver driver = new com.mysql.jdbc.Driver();
//1.2 注册驱动
DriverManager.registerDriver(driver);
//2. 获取数据库连接
String url = "jdbc:mysql://localhost:3306/dany";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url,user,password);
//3. 获取数据库操作对象
stmt = conn.createStatement();
//4. 执行SQL语句DQL语句->insert update delete
String sql_insert = "insert into emp_bak(empno,ename,job,mgr,hiredate,sal,comm,deptno) "
+ "values(7369,'Dany','CLERK',7788,'1992-08-22',900.00,100.00,10)";
//count = stmt.executeUpdate(sql_insert);
String sql_update = "update emp_bak set ename = 'DANY' where empno = 7369";
//count = stmt.executeUpdate(sql_update);
String sql_del = "delete from emp_bak where empno = 7369";
count = stmt.executeUpdate(sql_del);
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
}finally {
//6. 关闭资源
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
七、三种方法注册驱动
package Test1;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTest1 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
//1.第一种注册驱动方式
/*Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver);*/
//第二种注册驱动方式(静态代码块):通过java的反射机制创建注册驱动类
Class.forName("Test1.RegisterDriver");
//第三种方式注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取数据库连接
String url = "jdbc:mysql://localhost:3306/dany";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url,user,password);
//3. 获取数据库操作对象
stmt = conn.createStatement();
System.out.println(stmt);//打印对象
//4. 执行SQL语句DQL语句
String sql = "select e.ename,b.ename as leadername "
+ "from emp e "
+ "left join emp b "
+ "on e.mgr = b.empno";
rs = stmt.executeQuery(sql);
//5.处理查询结果集
while(rs.next()) {
String ename = rs.getString("ename");
String leadername = rs.getString("leadername");
System.out.println(ename + " " + leadername);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//6. 关闭资源
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
/**
* 注册驱动
* @author Danycym
*
*/
class RegisterDriver{
static {
try {
System.out.println("驱动类注册成功!");
Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
八、通过配置文件连接数据库
配置文件db.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/dany
user=root
password=root
代码
package Test1;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class JDBCTest2 {
public static void main(String[] args) throws IOException {
//-------从配置文件获取连接数据库的信息--------
//通过FileReader读取配置文件
FileReader fileReader = new FileReader("conf/db.properties");
//创建属性对象
Properties pro = new Properties();
//通过属性对象的load方法将配置文件的信息加载到内存中生成一个map集合
pro.load(fileReader);
//关闭流
fileReader.close();
String driver = pro.getProperty("driver");
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
//-------连接数据库--------
Connection conn = null;
Statement stmt = null;
int count = 0;
try {
//1.注册驱动
Class.forName(driver);
//2.获取数据库连接
conn = DriverManager.getConnection(url,user,password);
//3.获取数据库操作对象
stmt = conn.createStatement();
//4.执行SQL语句:DML -> insert update delete
String sql_insert = "insert into emp_bak(empno,ename,job,mgr,hiredate,sal,comm,deptno) "
+ "values(7377,'Cym','CLERK',7788,'1991-08-22',900.00,200.00,30)";
count = stmt.executeUpdate(sql_insert);
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
}finally {
//关闭资源
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
九、设置SQL语句框架,进行SQL语句预编译,执行DQL语句
package Test1;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCTest3 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//1. 注册驱动
//1.1 获取驱动对象
Driver driver = new com.mysql.jdbc.Driver();
//1.2 注册驱动
DriverManager.registerDriver(driver);
//2. 获取数据库连接
String url = "jdbc:mysql://localhost:3306/dany";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url,user,password);
//3.定义SQL语句框架
String sql = "select d.dname,e.ename "
+ "from emp e right join dept d "
+ "on e.deptno = d.deptno "
+ "where d.dname like ?";
//4.进行SQL语句预编译
ps = conn.prepareStatement(sql);
//5.进行赋值
ps.setString(1, "%s%");
//6.执行SQL语句
rs = ps.executeQuery();
//7.处理查询结果集
while(rs.next()) {
String dname = rs.getString("dname");
String ename = rs.getString("ename");
System.out.println(dname + " " + ename);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
//6. 关闭资源
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
十、通过SQL语句预编译执行DML语句
package Test1;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCTest4 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1. 注册驱动
//1.1 获取驱动对象
Driver driver = new com.mysql.jdbc.Driver();
//1.2 注册驱动
DriverManager.registerDriver(driver);
//2. 获取数据库连接
String url = "jdbc:mysql://localhost:3306/dany";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url,user,password);
//3.定义SQL语句框架:DML->insert
String sql = "insert into t_user(username,password) values(?,?)";
//4.进行SQL语句预编译
ps = conn.prepareStatement(sql);
//5.进行赋值
ps.setString(1, "zhangsan");
ps.setString(2, "123");
//6.执行SQL语句
int count = ps.executeUpdate();
System.out.println(count);
} catch (Exception e) {
e.printStackTrace();
}finally {
//6. 关闭资源
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
十一:事务
package Test1;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCTest5 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取数据库连接
String url = "jdbc:mysql://localhost:3306/dany";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url,user,password);
//关闭自动提交,开始了事务
conn.setAutoCommit(false);
//3.定义SQL语句:事务只与DML语句有关系
String sql = "delete from t_user where id = ?";
//4.进行SQL语句的预编译
ps = conn.prepareStatement(sql);
//5.进行赋值
ps.setInt(1, 2);
//6.执行SQL语句
int count = ps.executeUpdate();
System.out.println(count);
//提交事务
conn.commit();
} catch (Exception e) {
//事务回滚
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
//关闭资源
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}