环境准备一、下载JDBC驱动二、配置环境
1.新建项目2.导入MySQL驱动 三、简易示例
环境准备
Ubuntu 20.04
eclipse
MySQL
Java 1.8
一、下载JDBC驱动
- 在终端中使用 mysql -V查看MySQL版本
参考这篇博客从https://mvnrepository.com/artifact/mysql/mysql-connector-java下载对应版本的MySQL驱动下载后放置在Ubuntu系统合适的目录下
- 打开eclipse，依次点击：file->new->project选择Java目录下的Java project，然后设置项目名，如myMysql
- 在项目名右键，依次点击：build path->configure build path点击右侧 add external jars，选择之前下载的mysql-connector-java jar包，然后点击apply and close
- 在上述项目中新建package，命名为example在example包内新建class类 Sqltest，输入以下代码
package example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class SqlTest {
public static void main(String args[]) {
try {
//mysql 5.0版本驱动名
//Class.forName("com.mysql.jdbc.Driver"); //加载MYSQL JDBC驱动程序
//mysql 8.0版本驱动名
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("Success loading Mysql Driver!");
}catch (Exception e) {
System.out.print("Error loading Mysql Driver!");
e.printStackTrace();
}
try{
Connection connect = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/test","root","123456");
//连接URL为 jdbc:mysql//服务器地址/数据库名 ，后面的2个参数分别是登陆用户名和密码
System.out.println("Success connect Mysql server!");
Statement stmt = connect.createStatement();
ResultSet rs = stmt.executeQuery("select * from Student");
//executeQuery内为sql语句
//提前在数据库中建立表格
while (rs.next()) {
System.out.println(rs.getString("name"));
}
stmt.close();
}catch(Exception e) {
System.out.print("get data error!");
e.printStackTrace();
}
}
}
运行成功如图所示
- 在example包内新建class类 SqlCtrl，输入以下代码（需提前建立好对应的表格）
package example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlCtrl {
static class DBConnection {
String driver ="com.mysql.cj.jdbc.Driver";
String url= "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "123456";
public Connection conn;
public DBConnection() {
try {
Class.forName(driver);// 加载驱动程序
conn = (Connection) DriverManager.getConnection(url, user, password);// 连续数据库
if(!conn.isClosed())
System.out.println("Succeeded connecting to the Database!");
} catch (Exception e) {
e.printStackTrace();
}
}
public void close() {
try {
this.conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void main(String[] args){
add("acc", 45,89,100);
//update("Soft","lishishi@com","666");
show("Soft");
//del("王小五");
}
//插入操作
public static int add(String uname,int english,int math,int computer) {
int i=0;
String sql="insert into Student (Name,English,Math,Computer) values (?,?,?,?)";
DBConnection db = new DBConnection();
try {
PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
preStmt.setString(1, uname);
preStmt.setInt(2, english);
preStmt.setInt(3, math);
preStmt.setInt(4,computer);
preStmt.executeUpdate();
//Statement statement = (Statement) db.conn.createStatement();
//statement.executeUpdate(sql);
preStmt.close();
db.close();//关闭连接
} catch (Exception e) {
e.printStackTrace();
}
return i;//返回影响的行数，1为执行成功
}
//查找操作
public static void show(String name){
String sql ="select English from Student where Name='"+name+"'";
DBConnection db = new DBConnection();
//System.out.println("-----------------");
// System.out.println("姓名" +"t"+ "邮箱" +"t"+ "日期");
//System.out.println("-----------------");
try {
Statement stmt = (Statement) db.conn.createStatement();
ResultSet rs = (ResultSet) stmt.executeQuery(sql);
while(rs.next()){
//String uname = rs.getString("Name");
int english = rs.getInt("English");
//String uhiredate = rs.getString("hiredate");
//可以将查找到的值写入类，然后返回相应的对象
//这里 先用输出的端口显示一下
System.out.println("t"+ english );
}
rs.close();
db.close();//关闭连接
} catch (SQLException e) {
e.printStackTrace();
}
}
//更新操作
public static int update(String uname,String uemail,String upwd) {
int i =0;
String sql="update employee set email=?,pwd=? where name=?";
DBConnection db = new DBConnection();
try {
PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
preStmt.setString(1, uemail);
preStmt.setString(2, upwd);
preStmt.setString(3, uname);
preStmt.executeUpdate();
preStmt.close();
db.close();//关闭连接
} catch (SQLException e) {
e.printStackTrace();
}
return i;//返回影响的行数，1为执行成功
}
//删除操作
public static int del(String uname) {
int i=0;
String sql="delete from employee where name=?";
DBConnection db = new DBConnection();
try {
PreparedStatement preStmt = (PreparedStatement) db.conn.prepareStatement(sql);
preStmt.setString(1, uname);
preStmt.executeUpdate();
preStmt.close();
db.close();//关闭连接
} catch (SQLException e){
e.printStackTrace();
}
return i;//返回影响的行数，1为执行成功
}
}
