create database school;

use school;

create table student
(
  studentId      int         primary key  auto_increment  not null,
  studentName    varchar(10)                              not null,
  studentAge    int,
  studentPhone  varchar(15)
)

insert into student values(null,'Betty', '20', '00000000');
insert into student values(null,'Jerry', '18', '11111111');
insert into student values(null,'Betty', '21', '22222222');
insert into student values(null,'Steve', '27', '33333333');
insert into student values(null,'James', '22', '44444444');
commit;

mysqlpackage=com.mysql.jdbc.Driver
mysqlurl=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf-8
mysqlname=root
mysqlpassword=root

public class Student
{
  private int    studentId;
  private String  studentName;
  private int    studentAge;
  private String  studentPhone;
  
  public Student(int studentId, String studentName, int studentAge,
      String studentPhone)
  {
    this.studentId = studentId;
    this.studentName = studentName;
    this.studentAge = studentAge;
    this.studentPhone = studentPhone;
  }
  
  public int getStudentId()
  {
    return studentId;
  }

  public String getStudentName()
  {
    return studentName;
  }

  public int getStudentAge()
  {
    return studentAge;
  }

  public String getStudentPhone()
  {
    return studentPhone;
  }

  public String toString()
  {
    return "studentId = " + studentId + ", studentName = " + studentName + ", studentAge = " +
        studentAge + ", studentPhone = " + studentPhone;
  }
}

public class DBConnection
{  
  private static Properties properties = new Properties();
  
  static
  {
    /** 要从CLASSPATH下取.properties文件，因此要加"/" */
    InputStream is = DBConnection.class.getResourceAsStream("/db.properties");
    try
    {
      properties.load(is);
    } 
    catch (IOException e)
    {
      e.printStackTrace();
    }
  }
  
  /** 这个mysqlConnection只是为了用来从里面读一个PreparedStatement，不会往里面写数据，因此没有线程安全问题，可以作为一个全局变量 */
  public static Connection mysqlConnection = getConnection();
  
  public static Connection getConnection()
  {
    Connection con = null;
    try
    {
      Class.forName((String)properties.getProperty("mysqlpackage"));
      con = DriverManager.getConnection((String)properties.getProperty("mysqlurl"), 
          (String)properties.getProperty("mysqlname"), 
          (String)properties.getProperty("mysqlpassword"));
    } 
    catch (ClassNotFoundException e)
    {
      e.printStackTrace();
    } 
    catch (SQLException e)
    {
      e.printStackTrace();
    }
    return con;
  }
}

package com.xrq.test11;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StudentManager
{
  private static StudentManager instance = new StudentManager();
  
  private StudentManager()
  {
    
  }
  
  public static StudentManager getInstance()
  {
    return instance;
  }
  
  public List<Student> querySomeStudents(String studentName) throws Exception
  {
    List<Student> studentList = new ArrayList<Student>();
    Connection connection = DBConnection.mysqlConnection;
    PreparedStatement ps = connection.prepareStatement("select * from student where studentName = ?");
    ps.setString(1, studentName);
    ResultSet rs = ps.executeQuery();
    
    Student student = null;
    while (rs.next())
    {
      student = new Student(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4));
      studentList.add(student);
    }
    
    ps.close();
    rs.close();
    return studentList;
  }
}

List<Student> studentList = new ArrayList<Student>();
    studentList = StudentManager.getInstance().querySomeStudents("Betty");
    for (Student student : studentList)
      System.out.println(student);

studentId = 1, studentName = Betty, studentAge = 20, studentPhone = 00000000
studentId = 3, studentName = Betty, studentAge = 21, studentPhone = 22222222 

public List<Student> querySomeStudents(String studentName) throws Exception
{
  List<Student> studentList = new ArrayList<Student>();
  Connection connection = DBConnection.mysqlConnection;
  PreparedStatement ps = connection.prepareStatement("select * from student where studentName = '" + studentName + "'");
  ResultSet rs = ps.executeQuery();
    
  Student student = null;
  while (rs.next())
  {
    student = new Student(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString(4));
    studentList.add(student);
  }
    
  ps.close();
  rs.close();
  return studentList;
}

public static void main(String[] args) throws Exception
  {
    List<Student> studentList = new ArrayList<Student>();
    studentList = StudentManager.getInstance().querySomeStudents("' or '1' = '1");
    for (Student student : studentList)
      System.out.println(student);
  }

studentId = 1, studentName = Betty, studentAge = 20, studentPhone = 00000000
studentId = 2, studentName = Jerry, studentAge = 18, studentPhone = 11111111
studentId = 3, studentName = Betty, studentAge = 21, studentPhone = 22222222
studentId = 4, studentName = Steve, studentAge = 27, studentPhone = 33333333
studentId = 5, studentName = James, studentAge = 22, studentPhone = 44444444

public void addStudent(String studentName, int studentAge, String studentPhone) throws Exception
{
  Connection connection = DBConnection.mysqlConnection;
  PreparedStatement ps = connection.prepareStatement("insert into student values(null,?,?,?)");
  ps.setString(1, studentName);
  ps.setInt(2, studentAge);
  ps.setString(3, studentPhone);
  if (ps.executeUpdate() > 0)
    System.out.println("添加学生信息成功");
  else
    System.out.println("添加学生信息失败");  
}

public static void main(String[] args) throws Exception
{
  StudentManager.getInstance().addStudent("Betty", 17, "55555555");
}

Connection connection = DBConnection.mysqlConnection;
connection.setAutoCommit(false);

public void addStudent(String studentName, int studentAge, String studentPhone) throws Exception
{
  Connection connection = DBConnection.mysqlConnection;
  connection.setAutoCommit(false);
  PreparedStatement ps = connection.prepareStatement("insert into student values(null,?,?,?)");
  ps.setString(1, studentName);
  ps.setInt(2, studentAge);
  ps.setString(3, studentPhone);
  try
  {
    ps.executeUpdate();
    connection.commit();
  } 
  catch (Exception e)
  {
    e.printStackTrace();
    connection.rollback();
  }
}