JDBC学习

一、配置mysql

       在centos7.3的虚拟机上安装好mysql14.14后，需要配置user表才允许其他机器访问，代码如下：

       #mysql -u root -p

       mysql>use mysql;

       mysql>update user set host = '%' where user = 'root';

       mysql>select host, user from user;

       mysql>FLUSH   PRIVILEGES;

二、使用Navicat创建数据表，数据表脚本如下：

　　CREATE TABLE `NewTable` (
　　`id`  int(11) NOT NULL AUTO_INCREMENT ,
　　`name`  varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL ,
　　`sex`  int(1) NOT NULL ,
　　`age`  int(3) NOT NULL ,
　　PRIMARY KEY (`id`)
　　)
　　ENGINE=InnoDB
　　DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
　　AUTO_INCREMENT=1
　　ROW_FORMAT=DYNAMIC
　　;

三、导入数据库对应的jar包

　　在工程的图标上右击，选择”Properties(属性)”，在”Java Bulid Path(JAVA构建路径)”中的“Libraries(库)”中选择”Add External JARs…(添加外部JAR)”，选择相应的jar包。

　

　　以上这种方式引入的jar包都显示在根目录下，需要创建用户自定义的库才可以将jar包归类引用，操作步骤为BuildPath构建路径-->Configure Bulid Path配置构建路径 --> Libraries库-->Add Library添加库-->User Library用户库-->User Libraries用户库-->new新建：

　　选中创建的用户库，点击Add External JARs…添加外部jar...，创建的jar引用便实现归类。

 

四、UTF-8编码

　　1.修改数据库系统的编码

　　　　#mysql -u root -p

　　　　>show variables like 'character%'　　//查看当前的编码格式，character_set_database

　　　　>vim /etc/my.cnf　　//修改mysql的配置文件，在socket行下加入：character_set_server=utf8

　　　　>sudo systemctl restart mysqld　　重启mysql服务

　　　　>show variables like 'character%'　　//再次检查编码

　　2.修改数据库的编码

　　3.修改eclipse的编码：window窗口->preferences首选项->General常规->workspaceTypes工作空间->Text file encoding文本编码->Other其他->选择UTF-8->OK确定

　　4.代码中的数据库连接字符串修改为：jdbc:mysql://192.168.199.144:3306/tests?useUnicode=true&characterEncoding=utf-8

 

五、代码样例

　　1.实体类

package student;

public class StudentClass {
    private int id;
    private String name;
    private int sex;
    private int age;
    
    StudentClass(String Name,int Sex,int Age){
        this.name = Name;
        this.sex = Sex;
        this.age = Age;
    }
    
    public int getId(){
        return id;
    }
    
    public void setId(int Id){
        this.id = Id;
    }
    
    public String getName(){
        return name;
    }
    
    public void setName(String Name){
        this.name=Name;
    }
    
    public int getSex(){
        return sex;
    }
    
    public void setSex(int Sex){
        this.sex=Sex;
    }
    
    public int getAge(){
        return age;
    }
    
    public void setAge(int Age){
        this.age=Age;
    }
    
}

 

　　2.数据库操作代码

package student;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class jdbcstudent {

    public static void main(String[] args) {
        int insertresult = StudentInsert(new StudentClass("刘", 0, 8));
        int deleteresult = StudentDelete(1);
        System.out.println(insertresult);
        System.out.println(deleteresult);
        
        List<StudentClass> stuList = GetAllData();
        for(int i = 0; i < stuList.size(); i++)
        {
            String student = stuList.get(i).getName()+String.valueOf(stuList.get(i).getSex())+String.valueOf(stuList.get(i).getAge());
            System.out.println(student);
        }
    }
    
    //数据库连接
    private static Connection getConn(){
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://192.168.199.144:3306/tests?useUnicode=true&amp;characterEncoding=utf-8";
        String username = "root";
        String password = "SinoSoftadmin123";
        Connection conn = null;
        try
        {
            Class.forName(driver);
            conn = (Connection) DriverManager.getConnection(url,username,password);
        }
        catch(ClassNotFoundException ex){
            ex.printStackTrace();
        }
        catch(SQLException ec){
            ec.printStackTrace();
        }

        return conn;
    }
    
    //数据插入
    private static int StudentInsert(StudentClass student){
        Connection conn = getConn();
        int i = 0;
        String sql = "insert into students (name,sex,age) values (?,?,?)";
        PreparedStatement pstmt;
        try
        {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, student.getName());
            pstmt.setInt(2, student.getSex());
            pstmt.setInt(3, student.getAge());
            i = pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        }
        catch(SQLException ex){
            ex.printStackTrace();
        }
        return i;
    }
    
    //删除
    public static int StudentDelete(int id){
        Connection conn = getConn();
        int i=0;
        String sql = "delete from students where id = " + id;
        PreparedStatement pstmt;
        try
        {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            i=pstmt.executeUpdate();
            pstmt.close();
            conn.close();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
        }
        return i;
    }
    
    //查询
    public static List<StudentClass> GetAllData(){
        List<StudentClass> studentList = new ArrayList<StudentClass>();
        Connection conn = getConn();
        String sql = "select * from students";
        PreparedStatement pstmt;
        try
        {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next())
            {
                StudentClass stu = new StudentClass(rs.getString("name"),rs.getInt("sex"),rs.getInt("age"));
                if(stu != null)
                {
                    studentList.add(stu);
                }
            }
            
        }
        catch(SQLException ex){
            ex.printStackTrace();
        }
        return studentList;
    }

}