[digression]
To tell you the truth, I have been very confused about the experimental class of a hospital in lake. From freshman to junior, I feel that the experimental class basically depends on self-study and reading the blogs of senior students and sisters. What is the meaning of the existence of teachers? Maybe it's Tim blocking [doge]
If you open this blog, I think you should have a good understanding of what I said. Alas, it's not necessary to have known each other when we meet people who have fallen from the ends of the earth. I wish you success in passing the experimental acceptance.
[some notes]
I use idea and navicat, and the installation method will not be repeated here. It should be easy to find by searching, and then remember to add jar packages in the code Method of adding jar package in idea
As for why write this on the basis of many excellent predecessors who have published relevant blogs? Because the code of the predecessors did not fully meet the requirements of the teacher, they modified their code to the appearance required by the teacher after acceptance. So the teacher came to add blocking. He didn't explain the experimental requirements clearly before the experiment began, and then he punched my code when he accepted the experiment
be careful! I'm not sure that the modified code completely conforms to it, because I'm a little confused at the back. Yes, I'm a very unreliable senior. I'm really sorry. So let's do it first and change it later when we have a chance.
be careful! This article is for reference only!!!
[experiment content]
After installing and configuring the Mysql database, use the Java language to directly connect to the database through JDBC, create tables in the database, and add, delete, query and modify table data.
Key codes:
......
Class.forName(……); // Load database driver name
String url=……; // URL of the data connection
String name=……; // User name for database access
String password=……; // Password for database access
Connection con= DriverManager.getConnection(url, name, password);
......
After installing and configuring Mysql database, you need to use Java programming to complete the following tasks:
(1) Create the database table users, and the fields are username (primary key, varchar(10)) and pass(varchar(8)); In the database table person, the fields are username(varchar(10), corresponding to the username of the users table), name (primary key, varchar(20)), age(int, can be empty), teleno(char(11), can be empty); For example, if the user name is in the users table, there can be no corresponding user name data in the person table.
(2) Insert 4 rows of data in the users table, the data are (ly,123456), (liming,345678), (test, 11111), (test112345), and 3 rows of data in the person table, the data are (ly, Lei Li), (liming, Li Ming, 25), (test, test user, 2013388449933);
(3) Insert five rows of data into the person table, namely (ly, Wang Wu), (test2, test user 2), (test1, test user 1,33), (test, Zhang San, 2318877009966), (admin,admin). For the existing username in the table, modify its corresponding field value according to the latest data; If the username does not exist, insert the username in the users table with the default password of 888888 before inserting the data into the person table.
(4) Delete the username starting with test in the users table, and delete the corresponding data in the person table according to the rules.
It is required that the results after processing shall be printed on the console at each processing stage, and the format shall be output in tabular form, such as:
Table users
Field name xx field name xx
xx xx
Table person
Field name xx field name xx
xx xx
(5) Class design requirements: the connection and operation of the database need to be encapsulated for reuse in subsequent experiments. This item is a deduction item, and shall be deducted accordingly for the experiment without packaging.
Since the end of the experiment, the source code is submitted for acceptance as required.
code
The first is about library operations:
public class Ku {
Connection conn = null;
public void lianjie()
{
try {
//Load database driver
Class.forName("com.mysql.cj.jdbc.Driver");
//Get connection object
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1 "," / * database user name * / "," / * password * / ");
System.out.println("Database successfully connected!");
} catch (Exception e) {
e.printStackTrace();
System.out.println("Database connection failed!");
}
}
public void duankai()
{
try {
conn.close();
System.out.println("Database shutdown succeeded!");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
Then there are operations on the table:
public class Biao {
//Delete existing forms
public void shanbiao(String sql, String sql2, Connection conn)
{
Statement sta = null;
try {
sta = conn.createStatement();
sta.executeUpdate(sql);
sta.executeUpdate(sql2);
System.out.println("Initialization of form succeeded!");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
//New form
public void jianbiao(String sql, String sql2,Connection conn) throws Exception{
Statement sta = conn.createStatement();
sta.executeUpdate(sql);
sta.executeUpdate(sql2);
System.out.println("Form created successfully!");
}
}
Then there is the class of the properties of the users table:
public class Users {
private String username;
private String password;
//display information
public String getUsername() { return username; }
public String getPassword() { return password; }
//Modify information
public void setPassword(String password) { this.password = password; }
public void setUsername(String username) { this.username = username; }
//Easy to insert data
public String getUser() {
String temp="'"+this.username+"','"+this.password+"'";
return temp;
}
}
Then comes the class of the attribute of the person table:
public class Person {
private String username;
private String name;
private Integer age;
private String teleno;
//initialization
public void init() {
this.username=null;
this.name=null;
this.age=-1;
this.teleno=null;
}
//display information
public String getUsername() {return username;}
public String getName() {return name;}
public Integer getAge() {return age;}
public String getTeleno() {return teleno;}
//Modify information
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;
}
//Easy to insert data
public String getPerson() {
String temp="'"+this.username+"','"+this.name+"'";
if(this.getAge()!=-1) {
temp+=",'"+this.getAge()+"'";
}
if(this.getTeleno()!=null) {
temp+=",'"+this.getTeleno()+"'";
}
return temp;
}
}
Then there is the class of operations on the users table:
public class Userss {
public void zengusers(Statement state, Users user)
{
String sql="insert into users(username,password) values ("+user.getUser()+")";
try {
state.execute(sql);
System.out.println("insert users Data success!");
} catch (SQLException throwables) {
System.out.println("insert users Data failure!");
throwables.printStackTrace();
}
}
public void shanusers(Statement state, ResultSet rs, String test)
{
String sql="delete from users where username like'"+test+"%'";
try {
state.executeUpdate(sql);
System.out.println("delete users Table with"+test+"The data at the beginning is successful!");
} catch (SQLException throwables) {
throwables.printStackTrace();
System.out.println("delete users Table with"+test+"Data at the beginning failed!");
}
}
private String users1="surface users";
private String users2="username password";
public void xianusers(Statement state, ResultSet rs){
System.out.println(users1);
System.out.println(users2);
String sql="select * from users";
try {
rs=state.executeQuery(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
while (true){
try {
if (!rs.next()) break;
String s1=rs.getString(1);
String s2=rs.getString(2);
System.out.println(s1+" "+s2);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
Then comes the class of operation on the person table:
public class Personn {
public void shanperson(Statement state,ResultSet rs,String test)
{
String sql="delete from person where username like'"+test+"%'";
try {
state.executeUpdate(sql);
System.out.println("delete person Table with"+test+"The data at the beginning is successful!");
} catch (SQLException throwables) {
throwables.printStackTrace();
System.out.println("delete person Table with"+test+"Data at the beginning failed!");
}
}
//Delete specific value
public void shanchu(Statement state,String table,String atrribute,String value){
String sql="delete from "+table+" where "+atrribute+" = '"+value+"'";
//System.out.println(sql);
try {
state.executeUpdate(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
public void zengperson(Statement state, ResultSet rs, Person person)
{
//Discrimination initialization
String sql="insert into person(username,name";
if(person.getAge()!=-1 && person.getTeleno()!=null) { sql+=",age,teleno)"; }
else if(person.getAge()==-1 && person.getTeleno()==null) { sql+=")"; }
else if(person.getAge()!=-1 && person.getTeleno()==null) { sql+=",age)"; }
else { sql+=",teleno)"; }
sql+=" values ("+person.getPerson()+")";
//Check the person table
String sql1="select * from person where username='"+person.getUsername()+"';";
try {
rs=state.executeQuery(sql1);
if(!rs.next()){//non-existent
state.executeUpdate(sql);
System.out.println("insert person Data success!");
}
else{//existence
shanchu(state,"person","username",person.getUsername());
state.executeUpdate(sql);
System.out.println("Insert and update person Data success!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//Check the users table
String sql2="select * from users where username='"+person.getUsername()+"';";
try {
rs=state.executeQuery(sql2);
rs.last();
if(rs.getRow()==0){//non-existent
state.execute("insert into users(username,password) values ('"+person.getUsername()+"','888888')");
System.out.println("Insert and update person Data success!");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
private String person1="surface person";
private String person2="username name age teleno";
public void xianperson(Statement state,ResultSet rs){
System.out.println(person1);
System.out.println(person2);
String sql="select * from person";
try {
rs= state.executeQuery(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
while (true){
try {
if (!rs.next()) break;
String s1=rs.getString(1);
String s2=rs.getString(2);
String s3=rs.getString(3);
String s4=rs.getString(4);
System.out.println(s1+" "+s2+" "+s3+" "+s4);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
Finally, the class of the main function:
public class Mysqlconnect {
public static void main(String[] args){
//Link library
Ku lj=new Ku();
lj.lianjie();
//Create entity
Person person1=new Person();
Users user1=new Users();
Statement st = null;
try {
st = lj.conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
ResultSet rs = null;
try {
rs = st.getResultSet();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//Delete existing tables
Biao biao1=new Biao();
String user3="drop table users";
String person3="drop table person";
biao1.shanbiao(user3,person3, lj.conn);
System.out.println("Start the first step!");
//Create table
String users="create table users("+"username varchar(10) not null,"+"password varchar(8) not null,"+"primary key ( username )"+")";
String person ="create table person("+"username varchar(10) not null,"+"name varchar(20) not null,"+"age int,"+"teleno char(11),"+"primary key ( name )"+")";
try {
biao1.jianbiao(users,person, lj.conn);
} catch (Exception e) {
e.printStackTrace();
}
Userss userss1=new Userss();
Personn personn1=new Personn();
//Display table
userss1.xianusers(st,rs);
personn1.xianperson(st,rs);
String [][]user2={
{"ly","123456"},
{"liming","345678"},
{"test","11111"},
{"test1","12345"}};
String [][]person2={
{"ly","Lei Li",null,null},
{"liming","Li Ming","25",null},
{"test","Test user","20","13388449933"},
{"ly","Wang Wu",null,null},
{"test2","Test user 2",null,null},
{"test1","Test user 1","33",null},
{"test","Zhang San","23","18877009966"},
{"admin","admin",null,null}};
System.out.println("Start the second step!");
//Add users
for (int i=0;i<4;i++)
{
user1.setUsername(user2[i][0]);
user1.setPassword(user2[i][1]);
userss1.zengusers(st,user1);
}
for (int i=0;i<3;i++)
{
person1.init();
person1.setUsername(person2[i][0]);
person1.setName(person2[i][1]);
if(person2[i][2]==null) person1.setAge(-1);
else person1.setAge(Integer.valueOf(person2[i][2]).intValue());
person1.setTeleno(person2[i][3]);
personn1.zengperson(st,rs,person1);
}
userss1.xianusers(st,rs);
personn1.xianperson(st,rs);
System.out.println("Start the third step!");
//Add person
for (int i=3;i<8;i++)
{
person1.init();
person1.setUsername(person2[i][0]);
person1.setName(person2[i][1]);
if(person2[i][2]==null) person1.setAge(-1);
else person1.setAge(Integer.valueOf(person2[i][2]).intValue());
person1.setTeleno(person2[i][3]);
personn1.zengperson(st,rs,person1);
}
userss1.xianusers(st,rs);
personn1.xianperson(st,rs);
System.out.println("Start the fourth step!");
//Delete data starting with test
personn1.shanperson(st,rs,"test");
userss1.shanusers(st,rs,"test");
userss1.xianusers(st,rs);
personn1.xianperson(st,rs);
lj.duankai();
}
}