Practical experiment 1 of new programming technology

Purpose of the experiment:

Configure java programming environment, be familiar with java programming and java operation database

Experimental tasks:

  1. JDK installation, eclipse installation
  2. MYSQL database installation
  3. Connect to the database using eclipse
  4. Using java language to establish tables users and persons in the database
  5. Realize the functions of adding, deleting and printing in the table

Experimental process:

Task 1: JDK installation and eclipse installation

The JDK and eclipse versions in the given experimental instructions are not the latest. I suggest installing the latest version. Here is a web tutorial: JDK and eclipse installation

Task 2: MYSQL installation

It took me a whole afternoon and half an evening to install this database. The main reason is that the installation tutorial in the experimental instructions is inconsistent with the version. There are direct installation versions on the web page, but it seems that there are only 32-bit machines. The teacher said it doesn't matter, but I think there are other important issues. Therefore, I downloaded the 64 bit compressed version and need to install it in the form of windows instructions, The following is a 64 bit compressed version installation tutorial.
Download website: https://dev.mysql.com/downloads/mysql/

1. After downloading, unzip it to a folder (remember this path, which will be used later)

2. Create a txt file in the root directory with the name of my and the file suffix of ini
Then copy the following code and put it under the file (the newly unzipped file does not have a my.ini file, so you need to create it yourself)
The following codes need not be modified except the installation directory and data storage directory. (note!! note!!)
The modified path is the path you decompress!

[mysqld]
# Set 3306 port
port=3306
# Set the mysql installation directory ---------- is your file path-------------
basedir=D:\mysql\mysql-8.0.26-winx64
# Set the data storage directory of mysql database ----------- it is your file path and the data folder is created by yourself
datadir=D:\mysql\mysql-8.0.26-winx64\data
# Maximum connections allowed
max_connections=200
# Number of connection failures allowed.
max_connect_errors=10
# The character set used by the server is utf8mb4 by default
character-set-server=utf8mb4
# The default storage engine that will be used when creating new tables
default-storage-engine=INNODB
# The "mysql_native_password" plug-in authentication is used by default
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# Set the default character set of mysql client
default-character-set=utf8mb4
[client]
# Set the default port used by mysql client when connecting to the server
port=3306
default-character-set=utf8mb4


3. After configuring the file, run CMD as an administrator and remember to be an administrator

Enter the bin directory of mysql

4. Execute the command in the bin directory under MySQL Directory:

mysqld --initialize –console

copy root@localhost : save the password in the local folder (there is a space after: and do not copy it)

5. Install mysql service

mysqld --install mysql


6. Start mysql service

7. Connect to mysql

mysql -uroot -p

After entering, copy the password just saved and paste it to the command console

8. Enter the following command to change the password (change the new password to the password you want)

ALTER USER 'root'@'localhost' IDENTIFIED BY 'New password';


9. Configure environment variables
Click - control panel - > system and security - > advanced system settings - > Advanced - > environment variables
The path is your own installation location. Remember to modify it

10. Add the following code to the path

11. Now the installation of MySQL is complete. If you need to delete mysql, execute MySQL D -- install mysql. If you are prompted that the service already exists

The following code is deleted:

sc delete mysql

Task 3: connect to the database using eclipse

First, use eclipse to build the project, and build some classes in the source file directory. These classes are function implementation packages. My basic functions are as follows:

(classes are sorted in dictionary order, which is the default.) the first is the function of creating tables, the second is the function of linking databases, the third is the main function, the fourth song is the people table template, the fifth is to release database memory, the sixth is the implementation of some table operations, and the seventh is the users template.

Next, use eclipse to link the database, and directly go to the function description; (just read the code yourself)

package Net;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;

public class JdbcCon {
	private String dbUrl;
	private String dbUserName;//Database user name
	private String dbPassWord;//Database password
	private String jdbcName;//Drive name
//Constructor Initializers 
    public JdbcCon()
	{
		String bu=null;
		 String []name=null;
			//Read one character in turn, and return - 1 when there is none at the end. With separator
	        BufferedReader br = null;
			try {
				br = new BufferedReader(new FileReader("text.txt"));
			} catch (FileNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
				try {
					bu=br.readLine();
					name=bu.split(" ");
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		 dbUrl=name[0];
		 dbUserName=name[1];//Database user name
	     dbPassWord=name[2];;//Database password
		 jdbcName=name[3];;//Drive name
	}
	
	public Connection getCon()throws Exception{
		 
		Class.forName(jdbcName);//Load driver
		Connection con=DriverManager.getConnection(dbUrl, dbUserName, dbPassWord);//Establish connection
		return con;
	}
	
	public void CloseCon(Connection con)throws Exception{ //Close database connection
		if(con!=null) { 
			con.close();
			System.out.println("Disconnected from database!");
		}
	}
	
	public void connect() {
		JdbcCon jdncConn=new JdbcCon();
		try {
			jdncConn.getCon();
			System.out.println("Database connection succeeded!");
		} catch (Exception e) {  //Catch exception
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("Database connection failed!");
		}
	}
}

Note: file stream is not required in this experiment, but the teacher said that it is best to use file stream, because it is impossible for us to modify the source code when using the software, and the most we can modify is the data in the file. Therefore, it should be noted that the files in the code should be built under the project directory, otherwise an error will be reported. The following contents should be written in the file:

Task 4: create users and persons tables

Task description: create the database table users. 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.

First, we need to know how to manipulate the database through code. Among them, we need to establish a connection with the database. The above has been completed, and then we need to understand some database instructions. After understanding these instructions, we can transfer the string instructions to the database through the statement class and let the database execute the corresponding instructions.

For example, our instructions for creating the users table are as follows:

The above two strings are the instructions to create the users and persons tables. Just transfer the instructions to the database and let the database execute.
The following is the statement to transfer the instruction:

The string table is the above instruction. Here, the function parameter passing form is used.

Task 5: realize the functions of adding, deleting and printing in the table

First of all, to complete the realization of these functions, we must first learn some database instructions. As long as we convey these instructions to the database, we can realize the corresponding functions.
Add instruction:

Delete instruction:

This instruction is used to delete key fields. In the experimental requirements, users beginning with test need to be deleted, so this instruction is used;

This instruction deletes the specified value. Table is the table name, atrribute is the column name, and value is the column value. Delete the data.
After the instruction is written, it is better to use the object of statement class to pass it into the database for execution.

Printing function:

After the instruction is executed, the ResultSet class object is used to obtain the result set:

Print the form using the following code:

Experimental thoughts:

Write a project in java for the first time, and get started from a little white.
The encapsulation of this experiment is not very good, for example, users can not call functions to obtain database state, obtain result set, etc.

Experimental source code

Above are all classes

package Net;

import java.sql.SQLException;
import java.sql.Statement;

public class CreateTables {

	public void cttable(Statement state,String table) {
		String[] tablename = null;
		tablename=table.split(" ");//Find the name of the table
		try {
			state.executeUpdate(table);
			System.out.println("establish"+tablename[2]+"Table succeeded!");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("establish"+tablename[2]+"Table failed!");
			e.printStackTrace();
		}
	}
}


package Net;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;

public class JdbcCon {
	private String dbUrl;
	private String dbUserName;//Database user name
	private String dbPassWord;//Database password
	private String jdbcName;//Drive name
//Constructor Initializers 
    public JdbcCon()
	{
		String bu=null;
		 String []name=null;
			//Read one character in turn, and return - 1 when there is none at the end. With separator
	        BufferedReader br = null;
			try {
				br = new BufferedReader(new FileReader("text.txt"));
			} catch (FileNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
				try {
					bu=br.readLine();
					name=bu.split(" ");
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		 dbUrl=name[0];
		 dbUserName=name[1];//Database user name
	     dbPassWord=name[2];;//Database password
		 jdbcName=name[3];;//Drive name
	}
	
	public Connection getCon()throws Exception{
		 
		Class.forName(jdbcName);//Load driver
		Connection con=DriverManager.getConnection(dbUrl, dbUserName, dbPassWord);//Establish connection
		return con;
	}
	
	public void CloseCon(Connection con)throws Exception{ //Close database connection
		if(con!=null) { 
			con.close();
			System.out.println("Disconnected from database!");
		}
	}
	
	public void connect() {
		JdbcCon jdncConn=new JdbcCon();
		try {
			jdncConn.getCon();
			System.out.println("Database connection succeeded!");
		} catch (Exception e) {  //Catch exception
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("Database connection failed!");
		}
	}
}


package Net;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



public class Mainop {
	public static void main(String[] args){
		
		String userstabletemp="create table users ("
				+"username varchar(10) not null,"
				+"pass varchar(8) not null,"
				+"primary key ( username )"
				+")";
		
		String personstabletemp="create table persons ("
				+"username varchar(10) not null,"
				+"name varchar(20) not null,"
				+"age int,"
				+"teleph char(11),"
				+"primary key ( name )"
				+")";
		
		String [][]Userstemp={ {"ly","123456"},
				{"liming","345678"},
				{"test","11111"},
				{"test1","12345"}};
		
		String[][]Personstemp= {{"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}}; 
		
//Connect to database
		JdbcCon JC=new JdbcCon();
		JC.connect();
		
//Create person and user entity objects
		Persons person=new Persons();
		Users user=new Users();

		Statement state;
		try {
//The connected database was found
			state = JC.getCon().createStatement();
			ResultSet rs=state.getResultSet();
//Create a new table
			CreateTables ct=new CreateTables(); 
			ct.cttable(state, userstabletemp);
			ct.cttable(state, personstabletemp);
//Show the details of the table
			table_func func=new table_func(); 
			func.show_table(state, rs,"users");
			func.show_table(state, rs,"persons");
			
//Add users
			
			for(int i=0;i<4;i++) {
				user.SetUsername(Userstemp[i][0]);
				user.SetPass(Userstemp[i][1]);	
				func.adUers(state, user);
			}
			func.show_table(state, rs,"users");
//Add people	
			for(int i=0;i<8;i++) {
				person.initialize();
				person.SetUsername(Personstemp[i][0]);
				person.SetName(Personstemp[i][1]);
				if(Personstemp[i][2]==null) {
					person.SetAge(-1);
				}else {
					person.SetAge(Integer.valueOf(Personstemp[i][2]).intValue());
				}
				person.SetTeleno(Personstemp[i][3]);
				func.adPersons(state,rs,person);
				
			}
			func.show_table(state, rs,"users");
			func.show_table(state, rs,"persons");
			func.delete(state, rs,"test");
			func.show_table(state, rs,"users");
			func.show_table(state, rs,"persons");
			System.out.print("-------------Database initialization succeeded--------------------\n\n");
/*------------------------------Additional functions---------------------------*/			
			

/*------------------------------Free memory---------------------------*/	
			ReleaseStatement rel=new ReleaseStatement();
			rel.release(state, rs); 
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			JC.CloseCon(JC.getCon());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

package Net;

public class Persons {
	private String username;
	private String name;
	private int age;
	private String teleph;
	
	public void initialize() {
		this.username=null;
		this.name=null;
		this.age=-1;
		this.teleph=null;
	}
	
	public void SetUsername(String username) {
		this.username=username;
	}
	
	public void SetName(String name) {
		this.name=name;
	}
	
	public void SetAge(int age) {
		this.age=age;
	}
	
	public void SetTeleno(String teleno) {
		this.teleph=teleno;
	}
	
	public String getUsername() {
		return username;
	}
	
	public String getName() {
		return name;
	}
	
	public int getAge() {
		return age;
	}
	
	public String getTeleph() {
		return teleph;
	}
	
	public String getPerson() {
		String temp="'"+this.username+"','"+this.name+"'";
		if(this.getAge()!=-1) {
			temp+=",'"+this.getAge()+"'";
		}
		if(this.getTeleph()!=null) {
			temp+=",'"+this.getTeleph()+"'";
		}
		return temp;
	}
	
}


package Net;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ReleaseStatement {
	public void release(Statement state,ResultSet rs) {
		
		if(rs!=null) {
			try {
				rs.close();
				System.out.println("Released ResultSet!");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(state!=null) {
			try {
				state.close();
				System.out.println("Released Statement!");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}


package Net;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class table_func {
//Add users
	public void adUers(Statement state,Users user) {
		
		try {
			//String query = "SELECT * FROM persons where username='"+sql+"'"; 
			String sql="insert into users(username,pass) values ("+user.getUser()+")";
			
			state.executeUpdate(sql);
			System.out.println("insert user Data success!");
		} catch (SQLException e) {
			System.out.println("insert user Data failure!");
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

}
//Add people
	public void adPersons(Statement state,ResultSet rs,Persons person) {
    //Some fields that are empty need to be classified and discussed
		String sql="insert into persons(username,name";
		if(person.getAge()!=-1&&person.getTeleph()!=null) {
			sql+=",age,teleph)";
		}
		else if(person.getAge()==-1&&person.getTeleph()==null) {
			sql+=")";
		}
		else if(person.getAge()!=-1&&person.getTeleph()==null) {
			sql+=",age)";
		}
		else {
			sql+=",teleph)";
		}
		sql+=" values ("+person.getPerson()+")";
	
	//Check whether the name already exists in the person table according to the username
	String query ="select * from persons where username='"+person.getUsername()+"';";
	try {
		rs=state.executeQuery(query);
		if(!rs.next()) {//If it does not exist, data will be added directly
			state.executeUpdate(sql);
			System.out.println("insert person Data success!");
		}else { //When username exists, delete the original data first, and then insert a new data
			table_func func=new table_func(); 
			func.delete_detail(state, "persons", "username",person.getUsername());
			state.executeUpdate(sql);
			System.out.println("insert person Data successfully, and update data!");
		}
	} catch (SQLException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
	}
	
	//Check whether the name already exists in the user table according to the username
	String temp ="select * from users where username='"+person.getUsername()+"';";
	try {
		rs=state.executeQuery(temp);
		//rs.last();
		if(!rs.next()) {//If the username does not exist, a new piece of data will be added
			state.execute("insert into users(username,pass) values ('"+person.getUsername()+"','888888')");
			System.out.println("insert person The data is successfully and updated users Table data!");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		System.out.println("insert person Encountered obstacles in data processing!");
		e.printStackTrace();
	}
}
//Delete data in table
	public void delete(Statement state,ResultSet rs,String flag) {
	String query = "delete from users where username like '"+flag+"%'";//delete 
    try {
		state.executeUpdate(query);
		System.out.println("delete uses Table and"+flag+"The data at the beginning is successful!");
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		System.out.println("delete uses Table and"+flag+"Data at the beginning failed!");
		e.printStackTrace();
	}

    query = "delete from persons where username like '"+flag+"%'";//delete 
    try {
		state.executeUpdate(query);
	    System.out.println("delete persons Table and"+flag+"The data at the beginning is successful!");
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		System.out.println("delete persons Table and\"+flag+\"Data at the beginning failed!");
		e.printStackTrace();
	}
}

//This method is deleted according to the specific field value
    public void delete_detail(Statement state,String table,String atrribute,String value) {
	String query="delete from "+table+" where "+atrribute+" = '"+value+"'";
	try {
	state.executeUpdate(query);
	} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	}
}
//Print form
	public void show_table(Statement state,ResultSet rs,String tablename) {
	String query="select * from "+tablename;
	String datatemp=null;
	try {
		String tablename_head="*****************surface"+tablename+"***************";
		System.out.println(tablename_head);
		rs=state.executeQuery(query);//Find result set
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	try {
		System.out.print("|");//Print header
		for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
			System.out.print(String.format("%-12s", rs.getMetaData().getColumnLabel(i)));
		}
		System.out.print("|\n");
	} catch (SQLException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
	}
	try {
		while(rs.next()) {//Print table contents
			int i=1;
			System.out.print("|");
			while(i<=rs.getMetaData().getColumnCount()) {//Gets the number of columns in the table
			if(rs.getString(i)==null) datatemp="-";
			else datatemp=rs.getString(i);
			System.out.print( String.format("%-12s", datatemp));
			i++;
			}
			System.out.print("|\n");
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
	

}

package Net;

public class Users {
	private String username;
	private String pass;
	
	public void SetUsername(String username) {
		this.username=username;
	}
	
	public void SetPass(String pass) {
		this.pass=pass;
	}
	
	public String getUsername() {
		return username;
	}
	
	public String getPass() {
		return pass;
	}
	
	public String getUser() {    //Easy to use when inserting data
		String temp="'"+this.username+"','"+this.pass+"'";
		return temp;
		
	}
}

Tags: Java Database MySQL Eclipse

Posted on Wed, 29 Sep 2021 17:35:58 -0400 by ferrit91