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;
public MySQL() {
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);
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 addInfo(String name, String sno,
int age, String sex,
String sdept) {
String sql =
"insert into student(name, sno, age, sex, sdept) "
+
"values(‘" + name +
"‘, ‘" + sno +
"‘, " + age
+
", ‘" + sex +
"‘, ‘" + sdept +
"‘);";
try {
Class.forName(driver);
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, usrname, password);
ps = con.prepareStatement(sql);
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 = ‘" + name +
"‘;";
try {
Class.forName(driver);
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, usrname, password);
ps = con.prepareStatement(sql);
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 ‘" + name +
"‘;";
try {
Class.forName(driver);
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
con = DriverManager.getConnection(url, usrname, password);
ps = con.prepareStatement(sql);
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 +
".");
}
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 {
public static void main(String[] args) {
System.
out.println(
"Creating table student!");
MySQL sq =
new MySQL();
System.
out.println(
"Testing add data!");
sq.addInfo(
"Mike",
"2013210888",
22,
"male",
"CS");
System.
out.println(
"Testing delete data!");
sq.deleteInfo(
"Mike");
System.
out.println(
"Testing search data!");
sq.searchInfo(
"Mike");
System.
out.println(
"Success!");
}
}
利用JDBC连接MySQL并使用MySQL
标签：java jdbc mysql 数据库
