Java database connection (JDBC)

Posted by DoomyCheese on Mon, 20 Dec 2021 08:10:50 +0100

JDBC:
JDBC essence: it is actually a set of rules defined by the official (sun company) to operate all relational databases, that is, interfaces. Each database manufacturer implements this interface and provides database driver jar package. We can use this set of interfaces (JDBC) to program, and the real executed code is to drive the implementation classes in the jar package.
Quick start:

  1. Import the driver jar package, copy it to the project, and right-click Add as Library
  2. Register driver class forName(“com.mysql.jdbc.Driver”);
  3. Get database connection object connection conn = drivermanager getConnection
  4. Define SQL String sql = "UPDATE card SET card=card+500 WHERE NAME = 'li'";
  5. Gets the object Statement that executes the SQL Statement
  6. Execute SQL and accept the returned result Statement stmt = conn.createStatement();
  7. Processing results
  8. Release resource conn.close(); stmt.close();

Explain each object in detail:

  1. DriverManager: drive management objects
    Function: register driver and get database connection
Static method to get database connection
 Register driver Class.forName("com.mysql.jdbc.Driver");
static Connection getConnection(String ur1,String user,String password);
url:Specify the path of the connection jdbc:mysql://ip address: port number / database name
  1. Connection: database connection object
Get execution SQL Object of
Statement createStatement();
PreparedStatement PreparedStatement(String sql);

Management services:
Open transaction: void setAutoCommit(boolean autoCommit)Parameter is false Indicates that the transaction is started
 Commit transaction: commit();
Rollback transaction: rollback();

  1. Statement: object executing SQL
implement SQL
boolean execute(String sql)Can perform any SQL understand
int executeUpdate(String sql)implement DML(insert,update,delete),DDL(create,alter,drop)sentence,The return value is the number of rows affected
ResultSet executeQuery(String sql)implement DQL(select)sentence
  1. ResultSet: result set object
next()Move cursor down one line
getXXX(parameter)get data
  1. PreparedStatement: object that executes SQL (stronger than the former)
    SQL injection problem: when splicing SQL, some special keywords of SQL participate in string splicing. It will cause security problems
    Solve the SQL injection problem: use the PreparedStatement object to solve it
    usage method:

    1. When defining SQL, use? As a placeholder, such as select * from where username=? and password=?;
    2. Here? Assignment: setXXX (parameter 1, parameter 2) parameter 1 is? Starting from 1, parameter 2:? Value of
      Extract JDBC tool class: JDBC utils to simplify writing
      analysis:
  2. Extract registration driver

  3. Extract a method to get the connection object

  4. Extract a method to release resources

JDBC control transaction
Use the Connection object to manage transactions:

 1. Open transaction: setAutoCommit(boolean autoCommit)Call this method to set the parameter to falseļ¼ŒOpen transaction
 2. Rollback: commit()
 3. Submission: rollback()

Database connection pool
Concept: it is a container (Collection) for storing database connections, which can save resources and improve user access efficiency

C3P0:
Implementation: import two c3p0 jar packages and database driver jar packages, and define the configuration file c3p0 config XML or c3p0 properties
Standard interface: DataSource
Get DataSource ds=new ComboPooledDataSource(); The parameters not used are the default configuration, and the parameters are the specified configuration

java.sql Under the bag
 Get connection: getConnection()
Return connection: if connecting objects Connection Is obtained from the connection pool, then call close Method returns the connection

Druid:
realization:

  1. Import druid jar package;
  2. Define any name in the form of configuration file properties;
  3. Load the configuration file properties load(InputStream);
  4. Get connection pool object datasource DS = druiddatasourcefactory createDataSource(pro);
  5. Get connection using getConnection

Spring JDBC:Spring framework's simple encapsulation of JDBC. Provides a JDBC template object to simplify JDBC development

Use steps:
1. Import jar package
2. establish JdbcTemplate Object. Dependent on data source DataSource
	JdbcTemplate template=new JdbcTemplate(ds);
3. call JdbcTemplate Method to complete CURD Operation of
	 - updata()implement DML sentence. Addition, deletion and modification statement
	 - queryForMap()Query results encapsulate the result set as map Collection note: the length of the result set of this method query can only be 1
	 - queryForList()Query results encapsulate the result set as list Set encapsulates each record as Map Set, and then Map Load collection to List aggregate
	 - query()Query results, encapsulating the results as JavaBean Object, general use BeanPropertyRowMapper Implementation class example: List<Emp> list=template.query(sql,new BeanPropertyRowMapper<Emp>(Emp.class));
	 - queryForObject()Query results, encapsulating the results as objects,Generally used to implement aggregate functions

Topics: Java MySQL JDBC c3p0