A simple example of using jdbc to operate a database

1. Create the database users (Integer id, String name, String pass, String sex, Integer age) in mysql. 2. hierarchical implementation: cn. csdn. web. util encapsulates the Connction object cn. csdn. web. domain encapsulates the Entity bean cn. csdn. web. dao encapsulation interface and interface implementation class cn. csdn. web. the junit test class first uses the standalone mode to create a Connection object (remember to put the driver jar package in the lib folder) cn. csdn. web. util encapsulates the Connction object package cn. csdn. web. util; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatemen T; import java. SQL. resultSet; import java. SQL. SQLException; public class JdbcUtil {// define the database URL statement private static final String url = "jdbc: mysql: // localhost: 3306/3g? User = root & password = 211314 & useUnicode = true & characterEncoding = UTF8 "; // create the Connction object private static Connection conn = null in standbility mode; // load the drive public static Connection getConn () {if (conn = null) {try {Class. forName ("com. mysql. jdbc. driver "); conn = DriverManager. getConnection (url);} catch (ClassNotFoundException e) {// TODO Auto-generated catch blocke. printStackTrace ();} catch (SQLException e) {// TODO Auto-generat Ed catch blocke. printStackTrace () ;}return conn ;}// release resource method public static void release (ResultSet rs, PreparedStatement pstmt) {if (rs! = Null) {try {rs. close ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}} if (pstmt! = Null) {try {pstmt. close ();} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace () ;}}} encapsulate the Entity bean cn in the domain layer. csdn. web. util encapsulates the Connction object package cn. csdn. web. domain; public class Users {private Integer id; private String name; private String pass; private String sex; private Integer age; public Users () {super (); // TODO Auto-generated constructor stub} public Users (Integer id, St Ring name, String pass, String sex, Integer age) {super (); this. id = id; this. name = name; this. pass = pass; this. sex = sex; this. age = age;} public Integer getId () {return id;} public void setId (Integer id) {this. id = id;} public String getName () {return name;} public void setName (String name) {this. name = name;} public String getPass () {return pass;} public void setPass (String pass) {this. pass = pass;} publ Ic String getSex () {return sex;} public void setSex (String sex) {this. sex = sex;} public Integer getAge () {return age;} public void setAge (Integer age) {this. age = age ;}@ Overridepublic String toString () {return "Users [id =" + id + ", name =" + name + ", pass =" + pass + ", sex = "+ sex +", age = "+ age +"] ";}} encapsulation of Operation interfaces and interfaces in the domain Layer package cn. csdn. web. dao; import cn. csdn. web. domain. users; public interfac E UsersDao {boolean insert (Users entity); // Add boolean deleteById (Integer id); // Delete boolean updateById (Users entity); // modify Users findById (Integer id ); // query} cn. csdn. web. util encapsulates the Connction object package cn. csdn. web. dao; import java. SQL. connection; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import cn. csdn. web. util. jdbcUtil; import cn. csdn. web. domain. users; public clas S UsersDaoImpl implements UsersDao {// encapsulate the database object private static Connection conn; private PreparedStatement pstmt; private ResultSet rs; // Add public boolean insert (Users entity) {// declare the return value variable boolean flag = false; // obtain the connection object conn = JdbcUtil. getConn (); // defines the SQL statement String SQL = "insert into users (id, name, pass, sex, age) values (?,?,?,?,?) "; Try {// create a preprocessing object pstmt = conn according to the SQL statement. prepareStatement (SQL); // assign int index = 1 to the placeholder; pstmt. setObject (index ++, entity. getId (); pstmt. setObject (index ++, entity. getName (); pstmt. setObject (index ++, entity. getPass (); pstmt. setObject (index ++, entity. getSex (); pstmt. setObject (index ++, entity. getAge (); // execute update int I = pstmt.exe cuteUpdate (); if (I> 0) {flag = true ;}} catch (SQLException e) {// TODO Auto-generated catc H blocke. printStackTrace ();} // releases the JdbcUtil resource. release (rs, pstmt); return flag;} // Delete public boolean deleteById (Integer id) {// declare the return value variable boolean flag = false; // obtain the connection object conn = JdbcUtil. getConn (); // defines the SQL statement String SQL = "delete from users where id =? "; Try {// create a preprocessing object pstmt = conn according to the SQL statement. prepareStatement (SQL); // assign int index = 1 to the placeholder; pstmt. setObject (index ++, id); // execute update int I = pstmt.exe cuteUpdate (); if (I> 0) {flag = true ;}} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} // releases the JdbcUtil resource. release (rs, pstmt); return flag;} // change public boolean updateById (Users entity) {// declare the return value variable boolean flag = false; // obtain the connection object conn = JdbcUtil. getConn (); // Define the SQL statement String SQL = "update users set name = ?, Pass = ?, Sex = ?, Age =? Where id =? "; Try {// create a preprocessing object pstmt = conn according to the SQL statement. prepareStatement (SQL); // assign int index = 1 to the placeholder; pstmt. setObject (index ++, entity. getName (); pstmt. setObject (index ++, entity. getPass (); pstmt. setObject (index ++, entity. getSex (); pstmt. setObject (index ++, entity. getAge (); pstmt. setObject (index ++, entity. getId (); // execute update int I = pstmt.exe cuteUpdate (); if (I> 0) {flag = true ;}} catch (SQLException e) {// TODO Auto-generated catc H blocke. printStackTrace ();} // releases the JdbcUtil resource. release (rs, pstmt); return flag;} // query public Users findById (Integer id) {// declare the return value variable Users entity = new Users (); // obtain the connection object conn = JdbcUtil. getConn (); // defines the SQL statement String SQL = "select * from users where id =? "; Try {// create a preprocessing object pstmt = conn according to the SQL statement. prepareStatement (SQL); // assign int index = 1 to the placeholder; pstmt. setObject (index ++, id); // execute update rs = pstmt.exe cuteQuery (); if (rs. next () {entity. setId (rs. getInt ("id"); entity. setName (rs. getString ("name"); entity. setPass (rs. getString ("pass"); entity. setSex (rs. getString ("sex"); entity. setAge (rs. getInt ("age") ;}} catch (SQLException e) {// TODO Auto-generated catch blocke. printStackTrace ();} // releases the JdbcUtil resource. release (rs, pstmt); return entity ;}} junit test package cn. csdn. web. junit; import org. junit. test; import cn. csdn. web. dao. usersDao; import cn. csdn. web. dao. usersDaoImpl; import cn. csdn. web. domain. users; public class UsersTest {UsersDao uDao = new UsersDaoImpl (); @ Testpublic void insert () {Users entity = new Users (); entity. setName ("Test 1"); entity. setPass ("123"); entity. setSex ("female"); entity. setAge (21); boolean flag = uDao. insert (entity); if (flag) {System. out. println ("inserted successfully");} else {System. out. println ("insertion failed") ;}@ Testpublic void findById () {Users entity = uDao. findById (1); System. out. println (entity. toString () ;}@ Testpublic void update () {Users entity = uDao. findById (4); entity. setName ("Test 2"); entity. setAge (20); boolean flag = uDao. updateById (entity); if (flag) {System. out. println ("updated successfully");} else {System. out. println ("update failed") ;}@ Testpublic void deleteById () {boolean flag = uDao. deleteById (27); if (flag) {System. out. println ("deleted successfully");} else {System. out. println ("failed to delete ");}}}