Java basic learning Day5, JDBC connection to Mysql

Java basic learning Day5, JDBC connection to Mysql

Steps for JDBC to connect to Mysql

1. Loading drive
2. Create a connection
3. Get statement
4. implementation of sql
5. Close the connection

Give an example

Here we first create a class directly, and directly complete the above five steps in the main method of this class to realize JDBC connection to Mysql and call data.

public static void main(String[] args) {
	// TODO Auto-generated method stub
	
	try {
		//Load driver, in fact, creates objects through reflection
		Class.forName("com.mysql.cj.jdbc.Driver");
		//mysql can also be sql server or other databases
		//java10 is the name of the database localhost=127.0.0.1
		//java11 is the name of the database 
		//useSSL=false means no certificate is required 
		//serverTimezone represents time zone
		String url="jdbc:mysql://localhost:3306/java11?useSSL=false&serverTimezone=Asia/Shanghai";
	    //Login database user name
		String username="root";
		//Login database password
		String pwd="surfece2019DB";
		//This line of code is to create a connection
		Connection conn = DriverManager.getConnection(url,username,pwd);
		//After the connection is created, the Statement can be obtained. There are "extract ~" and other methods in the Statement, which are used to implement sql. They will be explained in detail later
		Statement st=conn.createStatement();
		ResultSet rs=st.executeQuery("select * from student");
		while(rs.next()){
			System.out.println("Name"+rs.getString("name"));
			System.out.println("Fraction"+rs.getString("score"));
		}
		//Remember to close the connection
		rs.close();
		st.close();
		conn.close();	
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}	
}

The above code is the implementation of jdbc connection to mysql, but it is not difficult to find that there are several parts of this code that can be reused. Therefore, we should encapsulate several steps to achieve code reuse.

Code encapsulation

First of all, by analyzing the above code, we can see that 1. Load driver 2. Create connection 5. Close connection these three steps are fixed. In actual use, what we really need to change is the change of statement acquisition and sql statement. Therefore, we put the steps 1, 3 and 5 into a class.

public class JDBCBasic {
	public static Connection getConnection() {
		Connection conn =null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			String url="jdbc:mysql://localhost:3306/java11?useSSL=false&serverTimezone=Asia/Shanghai";
			String username="root";
			String pwd="surfece2019DB";
			conn = DriverManager.getConnection(url,username,pwd);		
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return conn;		
	}
		
	public static void closeConn(Statement st,ResultSet rs,Connection conn) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(st!=null) {
			try {
				st.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}	
		if( conn!=null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
		}
	}
}

Next, the remaining two steps, because the expected result of each call is different, will use different sql statements, mainly including add, delete, change and query. So we first create a class whose member properties correspond to the expected output results, which is called at output time.
The Statement interface provides three methods to execute SQL statements: executeQuery, executeUpdate and execute. Which method to use is determined by the content generated by the SQL Statement. See the note for the specific use method. (in most cases, excuteQuery is used for query, and excuteUpdate is used for deletion, modification and addition)

public class Student {
	private int id; 
	private String sex;   
	private String cname; 
	private String mobile;
	private int score;
	private String name;
	
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getCname() {
		return cname;
	}
	public void setCname(String cname) {
		this.cname = cname;
	}
	public String getMobile() {
		return mobile;
	}
	public void setMobile(String mobile) {
		this.mobile = mobile;
	}
	public int getScore() {
		return score;
	}
	public void setScore(int score) {
		this.score = score;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", sex=" + sex + ", cname=" + cname + ", mobile=" + mobile + ", score=" + score
				+ ", name=" + name + "]";
	}
}

Next, we create a class in which we write methods to achieve the functions we expect. In the following example, four methods are put, corresponding to four sql statements: add, delete, modify and query.

public class StudentService {
	//This method is to put the query results of sql statements into the classes created above
	public List<Student> getAllStudent(String sql){
		List<Student> list=new ArrayList();
		Connection conn=JDBCBasic.getConnection();
		try {
			Statement st=conn.createStatement();
			//A statement used to produce a single result set, such as a SELECT statement.
			//excuteUpdate is used to execute INSERT, UPDATE, or DELETE statements and SQL DDL (data definition language) statements, such as CREATE TABLE and DROP TABLE. The effect of an INSERT, UPDATE, or DELETE statement is to modify one or more columns in zero or more rows in a table. The return value of executeUpdate is an integer indicating the number of rows affected (that is, the UPDATE count). For statements that do not operate on rows, such as CREATE TABLE or DROP TABLE, the return value of executeUpdate is always zero.
			//Execute is used to execute statements that return multiple result sets, multiple update counts, or a combination of the two. Most programmers don't need this advanced feature.
			ResultSet  rs=st.executeQuery(sql);
			while(rs.next()) {
				Student student=new Student();
				student.setName(rs.getString("name"));
				student.setId(rs.getInt("id"));
				student.setScore(rs.getInt("score"));
				student.setSex(rs.getString("sex"));
				list.add(student);
			}
			//Remember to close the connection
			JDBCBasic.closeConn(st, rs, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}

	//This method is to add data to the table
	public Student add(Student student) {
		Connection conn=JDBCBasic.getConnection();
		try {
			Statement st=conn.createStatement();		
			Random r=new Random();
			int id=r.nextInt(1000);
			student.setId(id);
			String sql="insert into student (id,sex,score,name) values ("+id+",'"+student.getSex()+"',"+student.getScore()+",'"+student.getName()+"')";
			st.executeUpdate(sql);
			JDBCBasic.closeConn(st, null, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return student;
	}
	
	//This method is to modify the data in the table
	public boolean updateNameById(String name,int id) {
		Connection conn=JDBCBasic.getConnection();
		boolean flag=false;
		try {
			Statement st=conn.createStatement();		
			String sql="update student set name='"+name+"' where id="+id;
			if(st.executeUpdate(sql)>0) {
				flag=true;
			};
			JDBCBasic.closeConn(st, null, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		
		return flag;
	}
	
	//Delete data in table
	public boolean deleteByName(String name) {
		Connection conn=JDBCBasic.getConnection();
		boolean flag=false;
		try {
			Statement st=conn.createStatement();
			String sql="delete from student where name='"+name+"'";
			if(st.executeUpdate(sql)>0) {
				flag=true;
			};
			JDBCBasic.closeConn(st, null, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
}

test

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		StudentService ss=new StudentService();
		Student student=new Student();
		student.setName("Alex2");
		student.setScore(100);
		student.setSex("male");
		ss.add(student);
		
		ss.deleteByName("allen");
		ss.updateNameById("harry2020", 2);
		String sql="select * from student";
		List<Student> ls=ss.getAllStudent(sql);
		for(Student s:ls) {
			//The toString method of the default execution object
			//The following print is the result returned by the s.toString method
			System.out.println(s);
		}
	}

Result

Student [id=1, sex=male, cname=null, mobile=null, score=89, name=Wang Wu]
Student [id=2, sex=female, cname=null, mobile=null, score=99, name=harry2020]
Student [id=3, sex=male, cname=null, mobile=null, score=79, name=Zhang San]
Student [id=37, sex=male, cname=null, mobile=null, score=100, name=Alex2]
Student [id=393, sex=male, cname=null, mobile=null, score=100, name=Alex2]

Reference: https://baike.baidu.com/item/statement/4771842? Fr = Aladdin × 2

Published 5 original articles, won praise 2, visited 35
Private letter follow

Tags: SQL Mobile MySQL JDBC

Posted on Sat, 11 Jan 2020 04:18:20 -0500 by erinther