練習最原始的JDBC的基本操作

拾慧發表於2018-04-22
JDBC

User.class

package com.humman.pojo;

public class User {
    private Integer userId;

    private String username;

    private Integer userAge;

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Integer getUserAge() {
        return userAge;
    }

    public void setUserAge(Integer userAge) {
        this.userAge = userAge;
    }

    @Override
    public String toString() {
        return "User [userId=" + userId + ", username=" + username + ", userAge=" + userAge + "]";
    }

}

UserService.class

package com.humman.service;

import java.util.List;

import com.humman.pojo.User;

public interface UserService {
    /**
     * 查詢所有列表資訊
     * @return
     */
    List<User> queryAll();

    /**
     * 插入User
     * @param user
     * @return
     */
    int insertUser(User user);
    /**
     * 更新User資訊
     * @param user
     * @return
     */
    int updateUser(User user);
    /**
     * 根據userId刪除對應資料
     * @param userId
     * @return
     */
    int deleteUserById(int userId);
}

UserServiceImpl

package com.humman.service.impl;

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

import com.humman.pojo.User;
import com.humman.service.UserService;
import com.humman.untils.DBuntil;

public class UserServiceImpl implements UserService{

    /**
     * 
     */
    public List<User> queryAll() {
        // TODO Auto-generated method stub
        String[] strs = null;
        ResultSet rs = null;
        List<User> userLsit =new ArrayList<User>();
        User user = null;
        try {
            rs =DBuntil.query("select * from T_users where ?=?",strs);
            while(rs.next()){
                user = new User();
                user.setUserId(rs.getInt("id"));
                user.setUsername(rs.getString("name"));
                user.setUserAge(rs.getInt("age"));
                userLsit.add(user);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            /**
             * 先關閉Connection，則rs也會被關閉
             * 若先關閉ResultSet，則無法再獲得Connection
             * 嘗試：可以先取得Connection和Statement物件，然後分別關閉，則不再有錯誤
             */
            Connection con=null;
            Statement sta= null;
            try {
                con = rs.getStatement().getConnection();
                sta = rs.getStatement();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

            DBuntil.closeRes(rs);
            DBuntil.closeSta(sta);
            DBuntil.closeCon(con);
        }
        return userLsit;

    }
    /**
     * 執行更新插入操作，預設返回0即插入失敗。
     */
    public int insertUser(User user) {
        // TODO Auto-generated method stub
        Object[] objs = {user.getUsername(),user.getUserAge()};
        try {
            return DBuntil.noQuery("insert into T_users (name,age) values (?,?)",objs);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return 0;
    }
    /**
     * 執行更新操作，傳入User物件，根據Id進行更新，更新name和age，未做細緻區分之更新name和age
     */
    public int updateUser(User user) {
        // TODO Auto-generated method stub
        Object[] objs = {user.getUsername(),user.getUserAge(),user.getUserId()};
        try {
            return DBuntil.noQuery("update T_users set name=?,age=? where id=?",objs);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return 0;
    }

    public int deleteUserById(int userId) {
        // TODO Auto-generated method stub
        Object[] userIds = {userId};
        try {
            return DBuntil.noQuery("delete from T_Users where id=?",userIds);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return 0;
    }

}

DBuntil.class

package com.humman.service.impl;

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

import com.humman.pojo.User;
import com.humman.service.UserService;
import com.humman.untils.DBuntil;

public class UserServiceImpl implements UserService{

/**
 * 
 */
public List<User> queryAll() {
    // TODO Auto-generated method stub
    String[] strs = null;
    ResultSet rs = null;
    List<User> userLsit =new ArrayList<User>();
    User user = null;
    try {
        rs =DBuntil.query("select * from T_users where ?=?",strs);
        while(rs.next()){
            user = new User();
            user.setUserId(rs.getInt("id"));
            user.setUsername(rs.getString("name"));
            user.setUserAge(rs.getInt("age"));
            userLsit.add(user);
        }
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }finally{
        /**
         * 先關閉Connection，則rs也會被關閉
         * 若先關閉ResultSet，則無法再獲得Connection
         * 嘗試：可以先取得Connection和Statement物件，然後分別關閉，則不再有錯誤
         */
        Connection con=null;
        Statement sta= null;
        try {
            con = rs.getStatement().getConnection();
            sta = rs.getStatement();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        DBuntil.closeRes(rs);
        DBuntil.closeSta(sta);
        DBuntil.closeCon(con);
    }
    return userLsit;

}
/**
 * 執行更新插入操作，預設返回0即插入失敗。
 */
public int insertUser(User user) {
    // TODO Auto-generated method stub
    Object[] objs = {user.getUsername(),user.getUserAge()};
    try {
        return DBuntil.noQuery("insert into T_users (name,age) values (?,?)",objs);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return 0;
}
/**
 * 執行更新操作，傳入User物件，根據Id進行更新，更新name和age，未做細緻區分之更新name和age
 */
public int updateUser(User user) {
    // TODO Auto-generated method stub
    Object[] objs = {user.getUsername(),user.getUserAge(),user.getUserId()};
    try {
        return DBuntil.noQuery("update T_users set name=?,age=? where id=?",objs);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return 0;
}

public int deleteUserById(int userId) {
    // TODO Auto-generated method stub
    Object[] userIds = {userId};
    try {
        return DBuntil.noQuery("delete from T_Users where id=?",userIds);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return 0;
}

}
簡單複習一下，也沒啥心得，就是記錄此時。
稍後會通過IO讀取dbproperties的配置

相關文章