PHP學習筆記——PHP腳本和JAVA連接mysql數據庫，學習筆記mysql

PHP學習筆記——PHP腳本和JAVA連接mysql數據庫，學習筆記mysql

環境

開發包：appserv-win32-2.5.10

服務器：Apache2.2

數據庫：phpMyAdmin

語言：php5，java

平台：windows 10

java驅動：mysql-connector-java-5.1.37

需求

編寫一個PHP腳本語言，連接到phpMyAdmin數據庫的test庫

編寫一個java web服務端，連接到phpMyAdmin數據庫的test庫

代碼

php連接方式

mysql.php

<?php
/*****************************
*數據庫連接
*****************************/
$conn = @mysql_connect("localhost","root","123");
if (!$conn){
    die("連接數據庫失敗：" . mysql_error());
}

mysql_select_db("test", $conn);
//字符轉換，讀庫
mysql_query("set character set utf8");
mysql_query("set names utf8");

?>

test.php測試

<?php 
    error_reporting(0);         //防止報錯
    include('mysql.php');
    $result=mysql_query("select * from user"); //根據前面的計算出開始的記錄和記錄數
    // 循環取出記錄
    $six;
    while($row=mysql_fetch_row($result))
    {    
    echo $row[0];
    echo $row[1];
    }
?>

 運行截圖 ：java 連接方式

1.新建一個java project為mysqlTest

2.加載JDBC驅動,mysql-connector-java-5.1.37

MySQLConnection.java

package com.mysqltest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/*
 * **Mysql連接**
 * 
 * 參數：
 * conn 連接
 * url mysql數據庫連接地址
 * user 數據庫登陸賬號
 * password 數據庫登陸密碼
 * 方法：
 * conn 獲取連接
 */
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";

    /*
     * 創建Mysql數據連接 第一步：加載驅動 Class.forName（Driver） 第二步：創建連接
     * DriverManager.getConnection(url, user, password);
     */
    public Connection conn() {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            System.out.println("驅動加載錯誤");
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            System.out.println("數據庫鏈接錯誤");
            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;

/*
 * mysql增刪改查
 */
public class Work {

    /*
     * insert 增加
     */
    public static int insert() {
        MySQLConnection connection = new MySQLConnection();
        Connection conns; // 獲取連接
        PreparedStatement pst; // 執行Sql語句
        int i = 0;
        String sql = "insert into user (username,password) values(?,?)";
        try {
            conns = connection.conn();
            pst = conns.prepareStatement(sql);
            pst.setString(1, "lizi");
            pst.setString(2, "123");
            i = pst.executeUpdate();
            pst.close();
            conns.close();
        } catch (SQLException e) {
            System.out.println("數據寫入失敗");
            e.printStackTrace();
        }
        return i;
    }

    /*
     * select 寫入
     */
    public static void select() {
        MySQLConnection connection = new MySQLConnection();
        Connection conns; // 獲取連接
        PreparedStatement pst; // 執行Sql語句(Statement)
        ResultSet rs; // 獲取返回結果
        String sql = "select * from user";
        try {
            conns = connection.conn();
            pst = conns.prepareStatement(sql);
            rs = pst.executeQuery(sql);// 執行sql語句
            System.out.println("---------------------------------------");
            System.out.println("名字        |        密碼");
            while (rs.next()) {
                System.out.println(rs.getString("username") + "        |        " + rs.getString("password"));
            }
            System.out.println("---------------------------------------");
            conns.close();
            pst.close();
            rs.close();
        } catch (SQLException e) {
            System.out.println("數據查詢失敗");
            e.printStackTrace();
        }
    }

    /*
     * update 修改
     */
    public static int update() {
        MySQLConnection connection = new MySQLConnection();
        Connection conns; // 獲取連接
        PreparedStatement pst; // 執行Sql語句(Statement)
        int i = 0;
        String sql = "update user set password = ? where username = ?";
        try {
            conns = connection.conn();
            pst = conns.prepareStatement(sql);
            pst.setString(1, "123");
            pst.setString(2, "lizi");
            i = pst.executeUpdate();
            pst.close();
            conns.close();
        } catch (SQLException e) {
            System.out.println("數據修改失敗");
            e.printStackTrace();
        }
        return i;

    }

    /*
     * delete 刪除
     */
    public static int delete() {
        MySQLConnection connection = new MySQLConnection();
        Connection conns; // 獲取連接
        PreparedStatement pst; // 執行Sql語句(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.executeUpdate();
            pst.close();
            conns.close();
        } catch (SQLException e) {
            System.out.println("數據刪除失敗");
            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截圖