PHP script and JAVA connection to mysql database

This article mainly introduces the PHP script and related information about connecting to the mysql database using JAVA. if you need it, you can refer to the following environment.

Development Kit: appserv-win32-2.5.10

Server: Apache2.2

Database: phpMyAdmin

Language: php5, java

Platform: windows 10

Java driver: mysql-connector-java-5.1.37

Requirement

Write a PHP script to connect to the test database of The phpMyAdmin database.

Write a java web Server that connects to the test database of The phpMyAdmin database

Code

Php connection method

Mysql. php

<? Php/****************************** database connection ****** * ********************/$ conn = @ mysql_connect ("localhost ", "root", "123"); if (! $ Conn) {die ("failed to connect to the database :". mysql_error ();} mysql_select_db ("test", $ conn); // character conversion, read database mysql_query ("set character set utf8 "); mysql_query ("set names utf8");?>

Test. php test

<? Php error_reporting (0); // prevent include ('MySQL. php '); $ result = mysql_query ("select * from user"); // calculate the initial record and number of records based on the previous calculation. // Retrieve the record in a loop $ six; while ($ row = mysql_fetch_row ($ result) {echo $ row [0]; echo $ row [1] ;}?>

Run:

Java connection

1. create a new java project named mysqlTest

2. load JDBC driver, mysql-connector-java-5.1.37

MySQLConnection. java

Package com. mysqltest; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. SQLException;/***** Mysql connection ***** parameter: * conn connection * url mysql database connection address * user database login account * password database login password * method: * conn get Connection */public class MySQLConnection {public static Connection conn = null; public static String driver = "com. mysql. jdbc. driver "; public static String url =" jdbc: mysql: // 127.0.0.1: 3306/post "; public static String user =" root "; public static String password =" 123 "; /** create Mysql data connection Step 1: load the driver Class. forName (Driver) Step 2: create a connection * DriverManager. getConnection (url, user, password); */public Connection conn () {try {Class. forName (driver);} catch (ClassNotFoundException e) {System. out. println ("driver loading error"); e. printStackTrace ();} try {conn = DriverManager. getConnection (url, user, password);} catch (SQLException e) {System. out. println ("database link error"); e. printStackTrace () ;}return conn ;}}

Work. java

Package com. mysqltest; import java. SQL. connection; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException;/** add, delete, modify, and query mysql */public class Work {/** insert add */public static int insert () {MySQLConnection connection Connection = new MySQLConnection (); connection conns; // Obtain the connection PreparedStatement pst; // execute the SQL statement int I = 0; String SQL = "insert into user (username, password) value S (?,?) "; Try {conns = connection. conn (); pst = conns. prepareStatement (SQL); pst. setString (1, "lizi"); pst. setString (2, "123"); I = pst.exe cuteUpdate (); pst. close (); conns. close ();} catch (SQLException e) {System. out. println ("data writing failed"); e. printStackTrace ();} return I;}/** select write */public static void select () {MySQLConnection connection = new MySQLConnection (); Connection conns; // Obtain the connection Prepared Statement pst; // execute the SQL Statement (Statement) ResultSet rs; // Obtain the returned result String SQL = "select * from user"; try {conns = connection. conn (); pst = conns. prepareStatement (SQL); rs = pst.exe cuteQuery (SQL); // execute the SQL statement System. out. println ("-------------------------------------"); System. out. println ("name | password"); while (rs. next () {System. out. println (rs. getString ("username") + "|" + rs. getString ("password");} S Ystem. out. println ("-------------------------------------"); conns. close (); pst. close (); rs. close ();} catch (SQLException e) {System. out. println ("Data Query failed"); e. printStackTrace () ;}/ ** update modify */public static int update () {MySQLConnection connection Connection = new MySQLConnection (); connection conns; // Obtain the Connection PreparedStatement pst; // execute the SQL Statement (Statement) int I = 0; String SQL = "update user set passw Ord =? Where username =? "; Try {conns = connection. conn (); pst = conns. prepareStatement (SQL); pst. setString (1, "123"); pst. setString (2, "lizi"); I = pst.exe cuteUpdate (); pst. close (); conns. close ();} catch (SQLException e) {System. out. println ("data modification failed"); e. printStackTrace ();} return I;}/** delete */public static int delete () {MySQLConnection connection = new MySQLConnection (); Connection conns; // get the connection PreparedS Tatement pst; // execute an SQL Statement (Statement) int I = 0; String SQL = "delete from user where username =? "; Try {conns = connection. conn (); pst = conns. prepareStatement (SQL); pst. setString (1, "lizi"); I = pst.exe cuteUpdate (); pst. close (); conns. close ();} catch (SQLException e) {System. out. println ("data deletion failed"); e. printStackTrace ();} return I;}/** test */public static void main (String [] args) {// System. out. println (insert (); select (); // System. out. println (update (); // System. out. println (delete ());}}

Test

Ps: php statements used to operate MySQL databases

We often use the conn. php file to establish a connection with the database, and then use include to call the file. This effectively prevents data calling errors caused by changes to database attributes.

Now let's take a look at the conn. php file. the code is as follows:

<? Php $ conn = @ mysql_connect ("localhost", "root", "") or die ("database connection error"); // link to the database server mysql_select_db ("messageboard ", $ conn); // select the database name messageboard mysql_query ("set names 'utf'"); // use utf encoding, which cannot be written as utf-otherwise, garbled characters are displayed, but UTF is case insensitive?>

I learned and collected several basic functions for operating MYSQL in PHP:

. Use the mysql_connect () function to connect to the MySQL server: mysql_connect ("hostname", "username", "password ");
For example, $ link = mysql_connect ("localhost", "root", "") or die ("cannot connect to the database server! The database server is not started, or the user name and password are incorrect! ". Mysql_error ());

. Use the mysql_select_db () function to select the database file: mysql_query ("use database name", $ link );

For example, $ db_selected = mysql_query ("use example", $ link );

. Use the mysql_query () function to execute the SQL statement: mysql_query (string query (SQL statement), $ link );

For example:

Add Member: $ result = mysql_query ("insert into tb_member values ('A','') ", $ link );

Modify member: $ result = mysql_query ("update tb_member setuser = 'B', pwd = ''Where user = 'a'", $ link );

Delete member: $ result = mysql_query ("delecte from tb_member where user = 'B'", $ link );

Query member: $ SQL = mysql_query ("select * from tb_book ");

Fuzzy query: $ SQL = mysql_query ("select * from tb_book where bookname like '%". trim ($ txt_book). "% '");

// The common character % indicates zero or any number of characters.

Display table structure: $ result = mysql_query ("DESC tb_member ");

. Use the mysql_fetch_array () function to obtain information from the array result set:

Syntax structure: array mysql_fetch_array (resource result [, int result_type])

The result parameter is a type parameter of the resource type. the integer parameter must be passed in the data pointer returned by the mysql_fetch_array () function;

Result_type: Optional. it is an integer parameter used to operate MySQL database statements in php. the input values are MYSQL_ASSOC (associated index) and MYSQL_NUM (digital index) MYSQL_BOTH (including the first two and the default values)

For example:

<>$sql=mysql_query("select * from tb_book");$info=mysql_fetch_object($sql);<>$sql=mysql_query("select * from tb_book where bookname like '%".trim($txt_book)."%'")；$info=mysql_fetch_object($sql);

. Use the mysql_fetch_object () function to obtain a row from the result set as an object:

Syntax structure: object mysql_fetch_object (resource result );

For example:

<>$sql=mysql_query("select * from tb_book");$info=mysql_fetch_object($sql);<>$sql=mysql_query("select * from tb_book where bookname like '%".trim($txt_book)."%'");$info=mysql_fetch_object($sql);

The mysql_fetch_object () function is similar to the mysql_fetch_array () function. only one difference is that an object instead of an array is returned. This function can only access the array by field name. Syntax structure of the row element in the access result set: $ row-> col_name (column name)

. Use the mysql_fetch_row () function to obtain each record in the result set row by row:

Syntax structure: array mysql_fetch_row (resource result)

For example:

<>$sql=mysql_query("select * from tb_book");$row=mysql_fetch_row($sql);<>$sql=mysql_query("select * from tb_book where bookname like '%".trim($txt_book)."%'");$row=mysql_fetch_row($sql);

. Use the mysql_num_rows () function to obtain the number of records in the result concentration:

Syntax structure: int mysql_num_rows (resource result)

For example:

$sql=mysql_query("select * from tb_book");......<?php $nums=mysql_num_rows($sql);echo $nums;?>

Note: To obtain the data affected by the insert, update, and delete statements, you must use the mysql_affected_rows () function.

. Mysql_query ("set names gb"); // set the encoding format of MySQL to the gb type to avoid garbled characters.

. Disable record set: mysql_free_result ($ SQL );

. Close the MySQL database server: mysql_close ($ conn );