目录
项目地址：
实验目的
安装、配置好Java编程环境、数据库环境，使用Java 进行编程以及数据库编程。
实验对应知识点
Java编译、运行，path、classpath环境变量，规范注释。数据库的JDBC驱动。
实验前任务
学习Java的基本语法以及数据库的理论基础。
实验要求及步骤
环境配置
此处我使用的是Eclpse最新版，JDK是官网下载的最新版，以及Mysql8.0带Workbench
Ecclipse
JAVA运行依赖的JDK
在安装完JDK后进行Java环境变量的配置，总共有三个环境变量需要进行配置，分别是JAVA_HOME、CLASSPATH以及Path变量。右击【计算机】—【属性】-----【高级系统设置】—【高级】—【环境变量】，然后点击系统变量中的【新建】，如图1.3所示进行JAVA_HOME环境变量的设置，其中变量值对应于具体的JDK安装目录（示例中JDK安装在c:\jdk1.7.0_51中）。然后同样的方式进行CLASSPATH变量的设置，其变量值为：.;%JAVA_HOME%\lib\dt.jar;%JAVA_HOME%\lib\toos.jar。而Path变量往往已经存在，先点击到Path变量，然后选择【编辑】，在Path变量值的最前面添加上%JAVA_HOME%\bin; 。也可以在CLASSPATH中加入所需的第三方类包的路径或自己编写的类路径，它们在之间用“;”间隔。
Mysql下载
然后下载数据库连接依赖的包
Java依赖的包
至此环境配置完成
设计思路
此实验要求使用java数据库编程实现对数据库的增删改查。实际本质就是用到了JDBC驱动，通过数据库url建立与数据库连接，然后利用Statement或者PreparedStatement的借口来上传并执行sql语句。
可先自行百度了解JAVA数据库编程基础知识。
一本字典：SQL语法基础
代码实现
代码框架
把数据表中的每一行数据映射到一个实例（Person类和User类）
每个表增删改查写一个类（PeosonOp类和UserOp类）
数据库连接以及执行SQL语句，此处使用了preparedstatement，其中一个接口执行增删改，返回操作成功记录数。另外一个结构执行查操作，返回一个ResultSet。最后PropertiesUtuil类负责读取config.properties文件中数据库的url，驱动名，以及数据库登陆账户密码。
Peoson类
package lab1;
public class Person {
private String username;
private String name;//主键
private Integer age;
private String teleno;
public Person(String username,String name,Integer age,String teleno){
this.username = username;
this.name = name;
this.age = age;
this.teleno = teleno;
}
public Person(String username,String name){
this(username,name,null,"");
}
public Person(String username,String name,Integer age){
this(username,name,age,"");
}
public Person(String username,String name,String teleno) {
this(username,name,null,teleno);
}
public String getUsername(){
return username;
}
public String getName(){
return name;
}
public Integer getAge(){
return age;
}
public String getTeleno(){
return teleno;
}
public void setUsername(String username){
this.username = username;
}
public void setName(String name){
this.name = name;
}
public void setAge(Integer age){
this.age = age;
}
public void setTeleno(String teleno){
this.teleno = teleno;
}
}
User类
package lab1;
public class User {
private String username;//主键
private String password;
public User(String username,String password){
this.username = username;
this.password = password;
}
public String getUsername() {
return username;
}
public String getPassword() {
return password;
}
public void setUsername(String username) {
this.username = username;
}
public void setPassword(String password) {
this.password = password;
}
}
PersonOp类
package lab1;
import java.sql.*;
public class PersonOp {
public void creatTable(DB_conn_op sjk) throws Exception{//创建表
String sql = "create table if not exists person"
+ "("
+ "username varchar(10) not null,"
+ "name varchar(20) not null,"
+ "age int default 18,"
+ "teleno char(11) default '18570253175',"
+ "primary key (name)"
+ ")";
int t = sjk.executeUpdate(sql);
if(t==0) System.out.println("person表已存在！");
else System.out.println("创建peoson表成功！");
}
public void addPerson(Person p,DB_conn_op sjk) throws Exception{//增加对象
UserOp uo = new UserOp();
if(findPerson(p,sjk)==true) {//person表中存在
updatePerson(p,sjk);
}
else {
//person表中不存在,先插入user表
if(!uo.findUser(p.getUsername(), sjk))
uo.addUser(new User(p.getUsername(), "888888"),sjk);
//插入person表中
String sql = "insert into person(username,name,age,teleno) values(?,?,?,?)";
Object[] obj = {p.getUsername(),p.getName(),p.getAge(),p.getTeleno()};
sjk.executeUpdate(sql,obj);
}
}
public void deletePerson(String username,DB_conn_op sjk) throws SQLException{//删除对象
String sql = "delete from person where username like ? ";
sjk.executeUpdate(sql,username);
}
public boolean findPerson(Person p,DB_conn_op sjk) throws Exception{//查询
String sql4 = "select * from person where username = ?";
ResultSet rs = sjk.query(sql4,p.getUsername());
return rs.next();
}
public void updatePerson(Person p,DB_conn_op sjk) throws Exception{//更新
String sql = "update person set name = ?,age = ?,teleno = ? where username = ?";
Object[] obj = {p.getName(),p.getAge(),p.getTeleno(),p.getUsername()};
sjk.executeUpdate(sql,obj);
}
public void Print_Table(DB_conn_op sjk) throws Exception{
System.out.println("表person");
System.out.println("字段名username\t字段名name\t字段名age\t\t字段名teleno");
System.out.println("-----------------------------------------------------------");
String sql = "select * from person";
ResultSet rs = sjk.query(sql);
while(rs.next()) {
String username = rs.getString("username");
String name = rs.getString("name");//主键
Integer age = rs.getInt("age");
if(age==0) age = null;
String teleno = rs.getString("teleno");
if(teleno.length()==0) teleno = null;
System.out.println(username + "\t\t" + name + "\t\t" + age +"\t\t"+teleno);
}
rs.close();
}
public void dropTable(DB_conn_op sjk) throws Exception{
String sql = "DROP TABLE person";
sjk.executeUpdate(sql);
}
}
UserOp类
package lab1;
import java.sql.ResultSet;
public class UserOp {
public void creatTable(DB_conn_op sjk) {//创建表
String sql = "create table if not exists users"
+ "( "
+ "username varchar(10) not null,"
+ "pass varchar(8) not null,"
+ "primary key (username)"
+ ")";
int t = sjk.executeUpdate(sql);
if(t == 0)System.out.println("users 表已存在！");
else System.out.println("创建 users 表成功！");
}
public void addUser(User u,DB_conn_op sjk) throws Exception{//增加数据
String sql = "insert into users(username,pass) values(?,?)";
Object[] obj = {u.getUsername(),u.getPassword()};
sjk.executeUpdate(sql,obj);
}
public void deleteUser_Username(String username,DB_conn_op sjk) throws Exception {//删除
String sql = "delete from users where username like ?";
sjk.executeUpdate(sql,username);
}
public boolean findUser(String username,DB_conn_op sjk) throws Exception {//查找
String sql = "select * from users where username = ?";
return sjk.query(sql,username).next();
}
public void updateUser(User u,DB_conn_op sjk) throws Exception{
String sql = "update person set pass = ? where username = ?";
Object[] obj = {u.getPassword(),u.getUsername()};
sjk.executeUpdate(sql,obj);
}
public void Print_Table(DB_conn_op sjk) throws Exception{
System.out.println("表users");
System.out.println("字段名username\t字段名pass");
System.out.println("-------------------------");
String sql = "select * from users";
ResultSet rs = sjk.query(sql);
while(rs.next()) {
String username = rs.getString("username");
String password = rs.getString("pass");//主键
System.out.println(username + "\t\t" + password);
}
}
public void dropTable(DB_conn_op sjk) throws Exception{
String sql = "DROP TABLE users";
sjk.executeUpdate(sql);
}
}
PropertiesUtil类
package lab1;
import java.io.InputStream;
import java.util.Properties;
public class PropertiesUtil {
public static String getValue(String key){
String ret = null;
try{
InputStream in = PropertiesUtil.class.getResourceAsStream("config.properties");
Properties properties = new Properties();
properties.load(in);
ret = properties.getProperty(key);
in.close();
}catch (Exception e){
e.printStackTrace();
}
return ret;
}
}
DB_conn_op类
package lab1;
import java.io.*;
import java.sql.*;
import java.text.SimpleDateFormat;
public class DB_conn_op {
private String JDBC_DRIVER = PropertiesUtil.getValue("JDBC_DRIVER");
private String DB_URL = PropertiesUtil.getValue("DB_URL");
private String USER = PropertiesUtil.getValue("USER");
private String PASS = PropertiesUtil.getValue("PASS");
private Connection con = null;
private File file = new File("../sql_log.txt");
private PreparedStatement pstmt = null;
OutputStream out = new FileOutputStream(file);
public DB_conn_op() throws Exception {
con = getConnection();
}
public Connection getConnection() {
Connection conn = null;
System.out.println("驱动加载中...");
try {
Class.forName(JDBC_DRIVER);
System.out.println("驱动加载成功！");
System.out.println("数据库链接中...");
conn = (Connection)DriverManager.getConnection(DB_URL,USER,PASS);
System.out.println("数据库链接成功！");
}catch (Exception e){
e.printStackTrace();
}
return conn;
}
public int executeUpdate(String sql,Object... params) {//插入返回操作的记录数
int rlt = 0;
try{
pstmt = con.prepareStatement(sql);
set_Params(pstmt,params);
rlt = pstmt.executeUpdate();
pstmt.close();
sql+="\n";
java.util.Date date=new java.util.Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String sj = sdf.format(date);
out.write(sj.getBytes());
out.write(sql.getBytes());
}catch (Exception e){
System.out.println("非法操作！");
//e.printStackTrace();
}
return rlt;
}
//设置参数
private void set_Params(PreparedStatement pstmt, Object[] params) throws Exception{
if(params!=null){
for(int i=0;i<params.length;i++){
if(params[i] instanceof String) pstmt.setString(i+1,(String)params[i]);
else if(params[i] instanceof Integer) pstmt.setInt(i+1,(Integer)params[i]);
else if(params[i] == null) pstmt.setNull(i+1, Types.INTEGER);
}
}
}
public ResultSet query(String sql,Object... params) throws Exception{//返回是否找到
pstmt = con.prepareStatement(sql);
set_Params(pstmt,params);
ResultSet rs = pstmt.executeQuery();
sql+="\n";
java.util.Date date=new java.util.Date();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String sj = sdf.format(date);
out.write(sj.getBytes());
out.write(sql.getBytes());
return rs;
}
public void close() throws Exception{
out.close();//关闭文件流
pstmt.close();
if(this.con!=null) {
this.con.close();
System.out.println("数据库已断开链接！");
}
}
}
配置文件
JDBC_DRIVER = com.mysql.cj.jdbc.Driver
DB_URL = jdbc:mysql://localhost:3306/lab1?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
USER = root
PASS = 123456
main，主要为了输出一个步骤化过程
package lab1;
public class main {
public static void main(String[] argc)throws Exception{
DB_conn_op sjk = new DB_conn_op();
UserOp uo = new UserOp();
PersonOp po = new PersonOp();
System.out.println("第一步：");
uo.creatTable(sjk);
po.creatTable(sjk);
uo.Print_Table(sjk);
po.Print_Table(sjk);
System.out.println("第二步：");
uo.addUser(new User("ly", "123456"),sjk);
uo.addUser(new User("liming", "345678"), sjk);
uo.addUser(new User("test", "11111"), sjk);
uo.addUser(new User("test1", "12345"), sjk);
po.addPerson(new Person("ly","雷力"), sjk);
po.addPerson(new Person("liming","李明",25), sjk);
po.addPerson(new Person("test","测试用户",20,"13388449933"), sjk);
uo.Print_Table(sjk);
po.Print_Table(sjk);
System.out.println("第三步：");
po.addPerson(new Person("ly","王五"), sjk);
po.addPerson(new Person("test2","测试用户2"), sjk);
po.addPerson(new Person("test1","测试用户1",33), sjk);
po.addPerson(new Person("test","张三",23,"18877009966"), sjk);
po.addPerson(new Person("admin","admin"), sjk);
uo.Print_Table(sjk);
po.Print_Table(sjk);
System.out.println("第四步：");
po.deletePerson("test%", sjk);
uo.deleteUser_Username("test%", sjk);
uo.Print_Table(sjk);
po.Print_Table(sjk);
sjk.close();
}
}
运行结果
总结
java其实和C++很像，不过java都是面向对象编程。验收的时候老师也说了主要看，是否全局只有一个connection（与数据库的连接），以及一些资源使用后是否关闭。就按照栈式规则（先使用的最后关闭）销毁使用的资源，比如preparedstatement，ResultSet等等。接下来实验二会发现System.out基本都没用！！！
577
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
