Connect java jdbc to mysql and javajdbcmysql
JDBC (Java Data Base Connectivity, java database connection) is a Java API used to execute SQL statements. It can provide unified access to multiple relational databases, it consists of a group of classes and interfaces written in Java. JDBC provides a standard API for database developers, allowing database developers to write database applications using pure Java APIs and run them across platforms without being limited by database vendors. Advantages:
- Easy to operate:Developers do not need to use complex drives to call commands and functions;
- High Portability:JDBC supports different relational databases
- Good versatility:The JDBC-ODBC bridge driver replaces the JDBC function with ODBC;
- Object-oriented:Common JDBC database connections can be encapsulated into a class and called directly when used.
Package DAO; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. resultSetMetaData; import java. SQL. SQLException; import java. util. arrayList; import java. util. collections; import java. util. hashMap; import java. util. list; import java. util. map; import com. mysql. jdbc. statement; public class JDBC {protected Connection conn; protected Statement; public static Connection getConnection () throws Exception {Connection connection = null; // create the Connection object try {Class for database Connection. forName ("com. mysql. jdbc. driver "); // load the Mysql DATA Driver/*** 127.0.0.1 indicates the local machine. If it is linked to a remote service machine, enter the default mysql Port Number of the remote machine ip 3306, test database name * user Database user Name password */connection = DriverManager. getConnection ("jdbc: mysql: // 127.0.0.1: 3306/test", "user", "password"); // create a data connection} catch (Exception e) {e. printStackTrace (); throw new Exception ("failed to link mysql Data");} return connection; // return the established database connection}/*** insert data records into mysql return the number of inserted data ** @ param SQL * SQL statement to be inserted * @ return count insert data number of * @ throws Exception */public int insert (String SQL) throws Exception {conn = getConnection (); // connect to the database try {statement = (Statement) conn. createStatement (); // create the Statement object int count = statement.exe cuteUpdate (SQL) for executing static SQL statements; // The SQL Statement conn for executing the insert operation. close (); // close the database connection return count; // return the number of inserted data} catch (SQLException e) {e. printStackTrace (); throw new Exception ("failed to insert data ");}} /*** update the number of records that meet the requirements ** @ param SQL * SQL statement for updating data * @ return count number of updated data * @ throws Exception */public int update (String SQL) throws Exception {conn = getConnection (); // connect to the database try {// create a Statement object for executing static SQL statements, statement = (Statement) conn. createStatement (); int count = statement.exe cuteUpdate (SQL); // SQL statement for executing the update operation, conn. close (); // close the database connection return count; // return the number of updated data} catch (SQLException e) {e. printStackTrace (); throw new Exception ("failed to update data") ;}}/*** query database, return the required record data ** @ param SQL statement for Data query * @ throws Exception * @ return list */public List <Object> query (String SQL) throws Exception {conn = getConnection (); // connect to the database try {statement = (Statement) conn. createStatement (); // create the Statement object ResultSet rs = statement.exe cuteQuery (SQL) for executing static SQL statements; // execute the SQL query Statement, returns the result set List <Object> list = ResultSetToList (rs); conn. close (); // close the database connection return list;} catch (SQLException e) {e. printStackTrace (); throw new Exception ("failed to query data") ;}/ * delete records that meet the requirements, output status * // ***** @ param SQL statement for deleting data * @ return count returns the number of deleted data * @ throws Exception */public int delete (String SQL) throws Exception {conn = getConnection (); // connect to the database try {statement = (Statement) conn. createStatement (); // create the Statement object int count = statement.exe cuteUpdate (SQL) for executing static SQL statements; // run the SQL Delete Statement conn. close (); // close the database connection return count; // return the number of deleted data} catch (SQLException e) {e. printStackTrace (); throw new Exception ("failed to delete data ");}} /***** query by page * @ param SQL the SQL statement to be searched * @ param page number * @ param count number of data entries * @ return List <Object> * @ throws Exception */public list <Object> findByPage (String SQL, int page, int count) throws Exception {conn = getConnection (); // connect to the database PreparedStatement pre = conn. prepareStatement (SQL); pre. setMaxRows (count); ResultSet rs = pre.exe cuteQuery (); if (page <1) {rs. absolute (0);} else {page = page * count-1; rs. absolute (page) ;}list <Object> List = ResultSetToList (rs); return list ;} /*** ResultSet to List */public List <Object> ResultSetToList (ResultSet rs) throws SQLException {if (rs = null) return Collections. emptyList (); ResultSetMetaData md = rs. getMetaData (); // get the structure information of the result set (rs) int columnCount = md. getColumnCount (); // returns the number of columns in the ResultSet Object List <Object> list = new ArrayList <Object> (); Map <Object, Object> rowData = new HashMap <Object, object> (); while (rs. next () {rowData = new HashMap <Object, Object> (columnCount); for (int I = 1; I <= columnCount; I ++) {rowData. put (md. getColumnName (I), rs. getObject (I);} list. add (rowData) ;}return list ;}}
How does one connect to a database using JDBC in java?
1. Registration driver
Class. forname ("com. mysql. jdbc. Driver"); // This is the Driver used to connect to the mysql database.
2. Get database connection
Java. SQL. Connection conn = java. SQL. DriverManager. getConnection (); 3. Get the expression
Java. SQL. Statement stmt = conn. createStatement ("jdbc: mysql: // localhost/test? UseUnicode = true & characterEncoding = GBK "," root "," null "); // the three parameters are the URL, user name, and password of the database connection. 4. Execute the SQL
Java. SQL. ResultSet rs1_stmt.exe cuteQuery ("select * from user"); 5. display the data in the result set
While (rs. next ()){
System. out. println (rs. getInt (1 ));
System. out. println (rs. getString ("username "));
System. out. println (rs. getString ("password "));
System. out. pringln ();
} // Execute the insert statement
// Stmt.exe cuteUpdate ("insert into user values (1, 'Chinese', '123 ')");
6. release resources
Rs. close ();
Stmt. close ();
Conn. close ();
Connect to the database using java jdbc
The create object is created by the keyword new. Here is just a declaration. It declares that con is a Connection type, and the actual create object is the code after the equal sign. The following familiar statements are the same User user = new User (); declare the type before the equal sign, and create an instance after the equal sign