JDBC使用sql语句

1. JDBC---连接数据库
   

java与数据库连接需要驱动，这个驱动则是JDBC，连接的时候需要ip+端口号+dbname 还要用户名和密码

1. 改java文件的编码方式--在项目名点右键属性----把编码方式改成UTF-8
   改变页面jsp的编码方式--在MyEclipse点右键--preferences中改
   点项目名右键属性：

建立4个包

必须要写这个方法：、

声明 执行

1. 当增、改、删的时候跟上面的程序差不多
   可是当查的时候只能
   

package com.softteem.dbweb.db;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
public class ConnDB {
public static void getConn(){

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url="jdbc:sqlserver://128.0.38.13:1433;databaseName=three";
            String user="sa";
            String password="sasa";
            Connection conn=DriverManager.getConnection(url, user, password);
            String sql="insert into teacher values('李老师',35,1)";
            conn.createStatement().execute(sql);
            conn.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public static void findAll(){
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url="jdbc:sqlserver://128.0.38.13:1433;databaseName=three";
            String user="sa";
            String password="sasa";
            Connection conn=DriverManager.getConnection(url, user, password);
            String sql="select * from teacher";
            ResultSet rs=conn.createStatement().executeQuery(sql);
            while(rs.next()){
                int tno=rs.getInt("tno");
                String tname=rs.getString("tname");
                int sex=rs.getInt("sex");
                int age=rs.getInt("age");
                System.out.println("tno="+tno+",tname="+tname+",sex="+sex+",age="+age);
            }
            conn.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    public static void main(String[] args) {
        findAll();
    }

}

1. 把相同代码合在一起
   
   
   
   

更简便的方法：

1. 作业
   

package com.softteem.dbweb.bean;

public class Teacher {
private int tno;
private String tname;
private int sex;
private int age;

public int getTno() {
    return tno;
}
public void setTno(int tno) {
    this.tno = tno;
}
public String getTname() {
    return tname;
}
public void setTname(String tname) {
    this.tname = tname;
}
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;
}

}
package com.softteem.dbweb.dao;

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

import com.softteem.dbweb.bean.Teacher;
import com.softteem.dbweb.db.ConnDB;

public class TeacherDao {

public static void insertOrUpdate(String sql){
    
    try {
        Connection conn=ConnDB.getConn();
        conn.createStatement().execute(sql);
        ConnDB.closeConn(conn);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    
}
public static List<Teacher> findAll(String sql){
    List<Teacher> list=new ArrayList<Teacher>();
    
    try {
        Connection conn=ConnDB.getConn();
        ResultSet rs=conn.createStatement().executeQuery(sql);
        while(rs.next()){
            int tno=rs.getInt("tno");
            String tname=rs.getString("tname");
            int sex=rs.getInt("sex");
            int age=rs.getInt("age");
            
            Teacher bean=new Teacher();
            bean.setTno(tno);
            bean.setTname(tname);
            bean.setSex(sex);
            bean.setAge(age);
            list.add(bean);
        }
        ConnDB.closeConn(conn);
    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return list;
}
public static void main(String[] args) {
    List<Teacher> list=findAll("select * from teacher");
    for (Teacher t : list) {
        System.out.println("编号："+t.getTno()+"姓名："+t.getTname()+"年龄："+t.getAge()+"性别："+t.getSex());
    }
}

}

package com.softteem.dbweb.db;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
public class ConnDB {
public static Connection getConn(){
Connection conn=null;
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://128.0.38.13:1433;databaseName=three";
String user="sa";
String password="sasa";
conn=DriverManager.getConnection(url, user, password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void closeConn(Connection conn){
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}