利用JDBC连接MySQL并使用MySQL
2024-03-30 23:38:17
driver为JDBC的驱动。
url为数据库的地址。
usrname和password分别为数据库的用户名和密码。
Connection类用来连接MySQL。
PreparedStatement类用来预编译sql语句，并可以执行。有的地方也用Statement。因为PreparedStatement继承了Statement，并且在执行多次查询更新的时候比Statement更快。PreparedStatement的第一次执行消耗是很高的，它的性能体现在重复执行。比如当我使用PreparedStatement来进行查询的时候，JDBC会发送一个网络请求来编译这个查询语句，在执行时会产生另外一个网络请求来执行。但是当我执行多次相同的查询的时候，使用PreparedStatement只会产生一个网络请求来执行语句。但如果使用Statement还会产生一个网络请求来编译语句。
ResultSet用来存储结果，一般用到select语句时用。
import java.sql.*;
class MySQL {
private static final String driver = "com.mysql.jdbc.Driver";
private static final String url = "jdbc:mysql://localhost:3306/school";
private static final String usrname = "root";
private static final String password = "";
private Connection con = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
//创建student表
public MySQL() {
//如果不存在Student表则创建
String sql = "create table if not exists Student(name char(10), "
+ "sno char(10) primary key, age smallint, sex char(6), "
+ "sdept char(4))";
//加载驱动
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, usrname, password);//连接数据库
ps = con.prepareStatement(sql);//预编译sql语句
ps.executeUpdate();//执行sql语句更新
//必须按顺序关闭并且必须关闭
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//插入多条信息
public void addInfo(String[] name, String[] sno, int[] age, String[] sex,
String[] sdept) {
String sql = "insert into student(name, sno, age, sex, sdept) "
+ "values(?, ?, ?, ?, ?);";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, usrname, password);
ps = con.prepareStatement(sql);//只需要预编译一次
for (int i = 0; i < name.length; i++) {
ps.setString(1, name[i]);
ps.setString(2, sno[i]);
ps.setInt(3, age[i]);
ps.setString(4, sex[i]);
ps.setString(5, sdept[i]);
ps.executeUpdate();
}
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//删除多条信息
public void deleteInfo(String[] name) {
String sql = "delete from student where name = ? ;";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, usrname, password);
ps = con.prepareStatement(sql);
for (int i = 0; i < name.length; i++) {
ps.setString(1, name[i]);
ps.executeUpdate();
}
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
//查询多条信息
public void searchInfo(String[] name) {
String sql = "select * from student where name like ? ;";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, usrname, password);
ps = con.prepareStatement(sql);
for (int i = 0; i < name.length; i++) {
ps.setString(1, name[i]);
rs = ps.executeQuery();//返回的查询结果存在结果集里面
if (rs.next()) {
do {
System.out.println("Name: " + rs.getString(1) + ", Sno: "
+ rs.getString(2) + ", age:" + rs.getString(3)
+ ", sex: " + rs.getString(4) + ", sdept: "
+ rs.getString(5));
} while (rs.next());
}
else {
System.out.println("There is no one named " + name[i] + ".");
}
}
try {
if (rs != null)
rs.close();
if (ps != null)
ps.close();
if (con != null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public class TestJDBC {
static String[] name = {"Mike", "Tom", "Jack", "Amy", "Allen"};
static String[] sno = {"2013210001", "2013210002", "2013210003", "2013210004", "2013210005", };
static int[] age = {21, 22, 25, 20, 24};
static String[] sex = {"female", "female", "female", "male", "male"};
static String[] sdept = {"CS", "EE", "SE", "CS", "SE"};
public static void main(String[] args) {
//创建Student表
System.out.println("Creating table student!");
MySQL sq = new MySQL();
//添加学生信息
System.out.println("\nTesting add data!");
sq.addInfo(name, sno, age, sex, sdept);
//删除Tom和Allen的信息
System.out.println("\nTesting delete data!");
String[] del = {"Tom", "Allen"};
sq.deleteInfo(del);
//查询Tom，Amy和Mike的信息。
System.out.println("\nTesting search data!");
String[] sear = {"Tom", "Amy", "Mike"};
sq.searchInfo(sear);
System.out.println("\nSuccess!");
}
}
上述代码运行结果如下
Creating table student!
Testing add data!
Testing delete data!
Testing search data!
There is no one named Tom.//因为上一部已经将Tom删除
Name: Amy, Sno: 2013210004, age:20, sex: male, sdept: CS
Name: Mike, Sno: 2013210001, age:21, sex: female, sdept: CS
Success!
最新文章
- http://www.microsoft.com/en-pk/download/details.aspx?id=40762
- ms08-067漏洞--初识渗透测试--想必很多初学者都会遇到我文中提及的各种问题
- HTML学习有感
- 基础笔记4（包装类，时间date. calendar
- SQL基础--完整性约束
- 黄聪：Wordpress 模版技术手册 - WordPress Theme Technical manuals
- windows下如何github ssh 公钥
- Eclipse10大快捷键组合
- IE-“无法浏览网页” 教你十招解决疑难杂症
- OSX 10.10+Xcode5.1 无法启动或者安装应用程序到iOS 6.1 simulator
- No row with the given identifier exists[ArtProject.Domains.Users#2]
- HTTP响应代码
- 多线程---其他方法 停止线程、守护线程、join方法
- oracle中新建用户和赋予权限
- 解决来自美国IP的攻击过程
- 二维数组的查找（JAVA）
- cdqz2017-test11-占卜的准备
- mfc标题栏 菜单 退出 关机 重启
- 第一个spring，第一天。
- Office 超级录屏如何旋转视频90度之后保存