数据库:mysql
连接方式:jdbc
文件组织结构:
create_db.java:
package jdbc;
import java.sql.*;
public class create_db {
/**
* 建立数据库连接,并创建一个新数据库,按照传入的参数 dbName命名
* @param dbName
*/
public static void createDB(String dbName) {
try {
// 1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn = null;
Statement statement = null;
try {
// 2.建立连接并创建数据库
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "root");
String sql1 = "drop database if exists " + dbName + ";";
String sql2 = "create database " + dbName + ";";
statement = conn.createStatement();
statement.execute(sql1);
statement.execute(sql2);
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} finally {
try {
if (statement != null)
statement.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
create_table.java:
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class create_table {
/**
* 在 dbName数据库中创建一个名为 tableName的表
* @param dbName
* @param tableName
*/
public static void createTable(String dbName, String tableName) {
try {
// 1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn = null;
Statement statement = null;
try {
// 2.建立连接并创建表
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/", "root", "root");
statement = conn.createStatement();
statement.execute("use " + dbName);
String sql3 = "create table " + tableName + "(id int not null,name varchar(20),sex varchar(4),age int)";
statement.execute(sql3);
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
} finally {
try {
if (statement != null)
statement.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
insert.java:
package jdbc;
import java.sql.*;
public class insert {
/**
* 向表里插入数据
* @param dbName
* @param tableName
*/
public static void insertData(String dbName, String tableName) {
try {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn = null;
PreparedStatement statement = null;
try {
// 连接数据库
String url = "jdbc:mysql://localhost:3306/" + dbName + "?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
//插入数据
String sql = "insert into " + tableName + "(id,name,sex,age) values (1,'张三','男',19),(2,'李四','女',18),(3,'王五','男',20),(4,'莫皓岚','男',21)";
statement = conn.prepareStatement(sql);
statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
if (statement != null)
statement.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
search.java:
package jdbc;
import student.Student;
import java.sql.*;
public class search {
/**
* 读取并打印表里所有的内容
* @param dbName
* @param tableName
*/
public static void Seek(String dbName, String tableName) {
try {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection conn = null;
PreparedStatement statement = null;
try {
// 连接数据库
String url = "jdbc:mysql://localhost:3306/" + dbName + "?useUnicode=true&characterEncoding=utf8";
String user = "root";
String password = "root";
conn = DriverManager.getConnection(url, user, password);
// 读取数据库
String sql1 = "select * from " + tableName;
statement = conn.prepareStatement(sql1);
ResultSet rs = statement.executeQuery(sql1);
Student student = null;
while (rs.next()) {
String id = rs.getString(1);
String name = rs.getString(2);
String sex = rs.getString(3);
int age = rs.getInt(4);
student = new Student(id, name, sex, age);
System.out.println(
student.getId() + " " + student.getName() + " " + student.getSex() + " " + student.getAge());
}
} catch (SQLException e) {
e.printStackTrace();
}
finally {
try {
if (statement != null)
statement.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Student.java:
package student;
public class Student {
private String id;
private String name;
private String sex;
private int age;
public Student(String id, String name, String sex, int age) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
}
public Student(String id) {
this.id = id;
this.name = null;
this.sex = null;
this.age = 0;
}
public String getId() {
return id;
}
public Student setId(String id) {
this.id = id;
return this;
}
public String getName() {
return name;
}
public Student setName(String name) {
this.name = name;
return this;
}
public String getSex() {
return sex;
}
public Student setSex(String sex) {
this.sex = sex;
return this;
}
public int getAge() {
return age;
}
public Student setAge(int age) {
this.age = age;
return this;
}
}
test.java:
package test;
import jdbc.create_db;
import jdbc.create_table;
import jdbc.insert;
import jdbc.search;
public class test {
public static void main(String[] args) {
create_db.createDB("test");
create_table.createTable("test", "student");
insert.insertData("test", "student");
search.Seek("test", "student");
}
}
pom.xml:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>jdbc_learning</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
</dependencies>
</project>